I've created a function which purpose is to import data to an excel report. This is however the first time I'm doing this, and I've exhausted all other options before asking the question here.
I call this function with this command: select ppr_data(2011,1,52,8) The issue relates to the variable "prosjektkode" ($4). When this is a singular digit the function runs as expected and the data appears correctly in the report. The issue appears when "prosjektkode" is multiple digits, i.e. 8,3,119 (i.e. I want run a report on multiple "prosjektkode"), when I do this it fails. I've tried to declare this variable as text and tried escaping the commas, but no luck. The function: CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int, prosjektkode int) RETURNS int AS $$ DECLARE antall bigint; BEGIN --Henter Inngang Antall Kunder select count(distinct a.kundenr) into antall from aktivitet a inner join utgave u on u.utgaveid=a.utgaveid inner join prosjekt p on p.prosjektkode=u.prosjektkode where a.utfort=1 and a.aktivtypekode in (82,83) and extract(year from a.utforesdato) = $1 -- Aarstall and extract(week from a.utforesdato) >= $2 -- Fra_uke and extract(week from a.utforesdato) <= $3 -- Til_uke and p.prosjektkode in ($4) and a.kundenr in ( select o.kundenr from ordrer o inner join utgave u on u.utgaveid=o.utgaveid inner join prosjekt p on p.prosjektkode=u.prosjektkode where o.ordretypenr in (1, 3, 4, 5) /* utelater ordretypen kredittordre */ and o.kreditert is null /* utelater krediterte ordre */ and o.ordrestatus in (3, 4) /* kun ordrer med status fakturert og klar til fakturert */ and o.ordresum > 0 /* Utelater 0-ordre og f.eks. messeeksemplar */ and extract(year from o.ordredato) = ($1 - 1) and p.prosjektkode in ($4) ); RETURN antall; END; $$ LANGUAGE plpgsql; -- View this message in context: http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4974235.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
