From: tlund79 <thomas.lund@eniro.no>
To: pgsql-sql@postgresql.org
Subject: Issue with a variable in a function
Date: Tue, 8 Nov 2011 05:16:53 -0800 (PST)
Message-ID: <1320758213629-4974235.post@n5.nabble.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: thomas.lund@eniro.no
Subject: Re: Issue with a variable in a function
In-Reply-To: <1320758213629-4974235.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox