Date: Tue, 8 Nov 2011 05:16:53 -0800 (PST)
From: tlund79 <thomas.lund@eniro.no>
To: pgsql-sql@postgresql.org
Message-ID: <1320758213629-4974235.post@n5.nabble.com>
Subject: Issue with a variable in a function
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.