PostgreSQL: Unable to assign "SELECT" query to variable within function

Question

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"...

Does your query work when you replace variables with actual values and try to run it directly (not wrapped in function)? — Tomas Greif
– Tomas Greif, Commented Apr 2, 2014 at 9:29
Yes and it also work when i remove "||" operator and use execute format. — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Apr 2, 2014 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
– pozs, Commented Apr 2, 2014 at 9:55

Erwin Brandstetter · Accepted Answer · 2014-04-03 04:57:06Z

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 plpgsql. 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.

Collectives™ on Stack Overflow

PostgreSQL: Unable to assign "SELECT" query to variable within function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related