Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am trying to assign a "SELECT" query to specific variable for further usage, but getting an error. The details as shown below with an example.

Example: Creating function with two parameters columnname and relationname and want to retrieve the rows accordingly.

--Function

create or replace function function1(columnname varchar,relationname varchar)
returns setof record as
$body$

declare
    --Declaration of variables used ahead
    str varchar;
    additionalcolumn varchar;
    grp varchar;
    selectquery varchar;
begin
    --IF condition to check "columnname" for first condition.
    if columnname='PhoneNo2' then
         str:='PhoneNo2';
         additionalcolumn:='Tower'||','||'State'||','||'Country';
         grp:='PhoneNo1'||','||columnname; 

          .
          . -- Other conditions 
          .

        end if;

       selectquery := 'select "PhoneNo1",'||str||', count(*) AS "Total-Calls"
        , sum("Duration") AS "Total-Duration"
        , count("TypeOfCall" = 'CALL-IN'  OR NULL) AS "Call-In"
        , count("TypeOfCall" = 'CALL-OUT' OR NULL) AS "Call-Out"
        , count("TypeOfCall" = 'SMS-IN'   OR NULL) AS "SMS-In"
        , count("TypeOfCall" = 'SMS-OUT'  OR NULL) AS "SMS-Out"
        , min("CallDate") AS "First-Call-Date"
        , max("CallDate") AS "Last-Call-Date"
        , (max("CallDate")-min("CallDate")) AS "Days"
        ,'||additionalcolumn|| 
           ' from '||relationname
        group by grp;

--Query "selectquery" execution
return query execute selectquery;

end;
$body$
language plpgsql;

--Function calling

select * from function1("PhoneNo2","table1") 
as ("PhoneNo1" varchar(20),"PhoneNo2" varchar(20), "Total-Calls" bigint,
"Total-Duration" bigint,"Call-In" bigint,"Call-Out" bigint, "SMS-In" bigint,
"SMS-Out" bigint,"First-Call-Date" date,"Last-Call-Date" date, "Days" bigint,
"Tower" varchar,"State" varchar,"Country" varchar);

--Error occurred

ERROR:  syntax error at or near "-"
LINE 46:    ', "count("TypeOfCall" = 'CALL-IN'  OR NULL) AS "Call-In"... 
share|improve this question
    
Does your query work when you replace variables with actual values and try to run it directly (not wrapped in function)? –  Tomas Greif Apr 2 at 9:29
    
Yes and it also work when i remove "||" operator and use execute format. –  Meem Apr 2 at 9:35
    
Even SO syntax highlight shows the error: you use ' inside another single-quoted string literal. escape it like '' - or use dollar quoted strings. –  pozs Apr 2 at 9:55
    
@pozs, Exactly right. –  Meem Apr 2 at 10:27
    
@pozs, For "group by" i have to include in single quote? –  Meem Apr 2 at 11:11

1 Answer 1

up vote 1 down vote accepted

Something along these lines would work:

CREATE OR REPLACE FUNCTION function1(columnname text, relationname regclass)
  RETURNS SETOF record AS
$func$
DECLARE
    str text;
    additionalcolumn text;
    grp text;
    selectquery text;
BEGIN
   --IF condition to check "columnname" for first condition.
   IF columnname = 'PhoneNo2' then
         str := ', "PhoneNo2"';                 -- double-quote
         additionalcolumn := 
               ', max("Tower") AS t, max("State") AS s, max("Country2") AS c';
         grp :='1, 2';              -- simpler with positional parameters
   ELSE                                         -- can't have NULL values 
         str := '';
         additionalcolumn := '';
         grp :='1'; 
   END IF;

RETURN QUERY EXECUTE         -- use dollar quoting to allow single quotes
   'select "PhoneNo1"' || str || $$, count(*) AS "Total-Calls"
    , sum("Duration") AS "Total-Duration"
    , count("TypeOfCall" = 'CALL-IN'  OR NULL) AS "Call-In"
    , count("TypeOfCall" = 'CALL-OUT' OR NULL) AS "Call-Out"
    , count("TypeOfCall" = 'SMS-IN'   OR NULL) AS "SMS-In"
    , count("TypeOfCall" = 'SMS-OUT'  OR NULL) AS "SMS-Out"
    , min("CallDate") AS "First-Call-Date"
    , max("CallDate") AS "Last-Call-Date"
    , max("CallDate") - min("CallDate") AS "Days" 
  $$ || additionalcolumn || '
   from ' || relationname || '
   group by ' || grp;

END
$func$  LANGUAGE plpgsql;

There are too many errors and problems to explain them all. Compare to your original to see what I changed. Search for related answers with the tag . Detailed explanation for each and every problem here has been posted in related answers.

Your life would be much easier with legal, lower-case identifiers.

share|improve this answer
    
Great! Thank You so much. –  Meem Apr 3 at 3:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.