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"...
'
inside another single-quoted string literal. escape it like''
- or use dollar quoted strings. – pozs Apr 2 at 9:55