Обсуждение: Issue with a variable in a function

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.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of tlund79
Sent: Tuesday, November 08, 2011 8:17 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Issue with a variable in a function


The issue relates to the variable "prosjektkode" ($4). 

CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int,
prosjektkode int) RETURNS int AS $$ DECLARE antall bigint;

-----------/Original Message----------

Read about "ARRAY"s

Change your function signature to something like:

CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int,
prosjektkode int[] )  -- Note the change to int[] from int for prosjektkode

David J.

I solved this one by trial and error. You were right I needed brackets to
indicate an array, but also needed to replace "in ($4)" with "= any ($4)"

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4977361.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.