Обсуждение: 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.
<div class="WordSection1"><p class="MsoPlainText">Thank you for your reply.<p class="MsoPlainText"> <p
class="MsoPlainText">I'vetried that and the function runs OK, my issue then is maybe selecting the function? I've tried
bothselect 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
readup about arrays and functions, but guess I'm missing the "functions for dummies" tutorial. :)<p
class="MsoPlainText"> <pclass="MsoPlainText">Please note that since posting this I've have changed some details such as
namingetc.<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> </span><pclass="MsoNormal"><b><span
lang="EN-US"style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US"
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">David Johnston [via PostgreSQL] [mailto:<a
href="/user/SendEmail.jtp?type=node&node=4977097&i=0"link="external" rel="nofollow" target="_top">[hidden
email]</a>]<br /><b>Sent:</b> 8. november 2011 18:28<br /><b>To:</b> Lund, Thomas<br /><b>Subject:</b> Re: Issue with a
variablein a function</span><p class="MsoNormal"> <p class="MsoNormal" style="margin-bottom:12.0pt">-----Original
Message-----<br />From: <a href="/user/SendEmail.jtp?type=node&node=4975030&i=0" link="external" rel="nofollow"
target="_top">[hiddenemail]</a> [mailto:<a href="/user/SendEmail.jtp?type=node&node=4975030&i=1"
link="external"rel="nofollow" target="_top">[hidden email]</a>] <br />On Behalf Of tlund79 <br />Sent: Tuesday,
November08, 2011 8:17 AM <br />To: <a href="/user/SendEmail.jtp?type=node&node=4975030&i=2" link="external"
rel="nofollow"target="_top">[hidden email]</a><br />Subject: [SQL] Issue with a variable in a function <br /><br /><br
/>Theissue relates to the variable "prosjektkode" ($4). <br /><br />CREATE OR REPLACE FUNCTION ppr_data(aarstall int,
fraukeint, tiluke int, <br />prosjektkode int) RETURNS int AS $$ DECLARE antall bigint; <br /><br
/>-----------/OriginalMessage---------- <br /><br />Read about "ARRAY"s <br /><br />Change your function signature to
somethinglike: <br /><br />CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int, <br />prosjektkode
int[]) -- Note the change to int[] from int for prosjektkode <br /><br />David J. <br /><br /><br /><br />-- <br
/>Sentvia pgsql-sql mailing list (<a href="/user/SendEmail.jtp?type=node&node=4975030&i=3" link="external"
rel="nofollow"target="_top">[hidden email]</a>) <br />To make changes to your subscription: <br /><a href=""
link="external"rel="nofollow" target="_top">click here</a>.<br /><a
href="http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespace&breadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml"
link="external"rel="nofollow" target="_top">See how NAML generates this email</a></div><br /><hr align="left"
width="300"/> View this message in context: <a
href="http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4977097.html">RE:Issue with
avariable in a function</a><br /> Sent from the <a
href="http://postgresql.1045698.n5.nabble.com/PostgreSQL-sql-f2142323.html">PostgreSQL- sql mailing list archive</a> at
Nabble.com.<br/>
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.