pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with a variable in a function
4+ messages / 2 participants
[nested] [flat]
* Issue with a variable in a function
@ 2011-11-08 13:16 tlund79 <thomas.lund@eniro.no>
0 siblings, 1 reply; 4+ messages in thread
From: tlund79 @ 2011-11-08 13:16 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Issue with a variable in a function
@ 2011-11-08 17:27 David Johnston <polobo@yahoo.com>
parent: tlund79 <thomas.lund@eniro.no>
0 siblings, 1 reply; 4+ messages in thread
From: David Johnston @ 2011-11-08 17:27 UTC (permalink / raw)
To: 'tlund79' <thomas.lund@eniro.no>; pgsql-sql
-----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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Issue with a variable in a function
@ 2011-11-09 08:32 tlund79 <thomas.lund@eniro.no>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: tlund79 @ 2011-11-09 08:32 UTC (permalink / raw)
To: pgsql-sql
Thank you for your reply.
I've tried that and the function runs OK, my issue then is maybe selecting the function? I've tried both select ppr_pf_inn_antall(2011,1,52,[8,3]) and select ppr_pf_inn_antall(2011,1,52,{8,3}) but none of them runs. I've read up about arrays and functions, but guess I'm missing the "functions for dummies" tutorial. :)
Please note that since posting this I've have changed some details such as naming etc.
From: David Johnston [via PostgreSQL] [mailto:ml-node+s1045698n4975030h80@n5.nabble.com]
Sent: 8. november 2011 18:28
To: Lund, Thomas
Subject: Re: Issue with a variable in a function
-----Original Message-----
From: [hidden email]</user/SendEmail.jtp?type=node&node=4975030&i=0> [mailto:[hidden email]</user/SendEmail.jtp?type=node&node=4975030&i=1>]
On Behalf Of tlund79
Sent: Tuesday, November 08, 2011 8:17 AM
To: [hidden email]</user/SendEmail.jtp?type=node&node=4975030&i=2>
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.
--
Sent via pgsql-sql mailing list ([hidden email]</user/SendEmail.jtp?type=node&node=4975030&i=3>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
________________________________
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4975030.html
To unsubscribe from Issue with a variable in a function, click here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=...;.
See how NAML generates this email<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_h...;
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4977097.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Issue with a variable in a function
@ 2011-11-09 10:41 tlund79 <thomas.lund@eniro.no>
parent: tlund79 <thomas.lund@eniro.no>
0 siblings, 0 replies; 4+ messages in thread
From: tlund79 @ 2011-11-09 10:41 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-11-09 10:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-08 13:16 Issue with a variable in a function tlund79 <thomas.lund@eniro.no>
2011-11-08 17:27 ` David Johnston <polobo@yahoo.com>
2011-11-09 08:32 ` tlund79 <thomas.lund@eniro.no>
2011-11-09 10:41 ` tlund79 <thomas.lund@eniro.no>
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