[SQL] Using variables in select
Hi, Firstly; I'm not a developer, but a business developer and statistics guy. This means that I'm happy using "basic" selects to gather information for analysis. One thing that could really efficiate my work is using variables, let's say I have a table with different orders; |customer_id|productid|price| What I really want is to create a general query where I could easily substitute productid. // declare variable --> productid = 10 select count(distinct customer_id), avg(price) from customer_table where productid= \\ Is there a way of doing this without getting involved with heavy programming? Thank you and applogies if this is a ridicioulus question! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Using-variables-in-select-tp4692865p4692865.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 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. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Issue with a variable in a function
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:[email protected]]
Sent: 8. november 2011 18:28
To: Lund, Thomas
Subject: Re: Issue with a variable in a function
-Original Message-
From: [hidden email]
[mailto:[hidden email]]
On Behalf Of tlund79
Sent: Tuesday, November 08, 2011 8:17 AM
To: [hidden email]
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])
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=4974235&code=dGhvbWFzLmx1bmRAZW5pcm8ubm98NDk3NDIzNXwtMjE0NTgyMTQ1Mw==>.
See how NAML generates this
email<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>
--
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.
Re: [SQL] Issue with a variable in a function
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. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Returning data from multiple functions
A short brief about the goal: I have a Excel workbook loaded with static data and need to compare these to "live-data" from DB. There are 20 rows with data in the report that needs to be retrieved from the database, and almost the same amount of queries, since all of these queries relies on variable input I've created on function per cell. This solution works both in terms of maintenance and less "query work in Excel". However, all of these functions do have the same variable input, thus I was hoping to create one function that runs all of the functions and input the variables. This enables me to make the excel workbook maintenance free and all of the logic is put in one place. This is were I'm stuck, creating and selecting one and one function works fine, but creating one function to run and retrieve data from multiple functions seems like a challenge. What I basically want is a 'union all', but when I try to run a union all on the functions it only retrieves data from the last function. This is what I got now (i've inputed the variables manually in each function for testing purposes): CREATE OR REPLACE FUNCTION ppr_test_to_funk() RETURNS SETOF RECORD AS $BODY$ DECLARE ppr RECORD; BEGIN select ppr_pf_inn_verdi(2011,1,52,array[3], array[7,4]) union all select ppr_pf_inn_antall(2011,1,52,array[3], array[7,4]); RETURN NEXT ppr; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000; ALTER FUNCTION ppr_test_to_funk() OWNER TO oystein; When I select this one I get the usual "Query has no destination for result data". If my suspicions are correct I'm struggelig with the "RETURNS SETOF" combined with the destination. And yes; I'm totally new to all of this. Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/Returning-data-from-multiple-functions-tp4980747p4980747.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Returning data from multiple functions
I know got this far thanks to Pavle Stehule. The function worked and returned the data when the variables was predefined after "return query". When tried to replace these with variables passed through the function call I got this message; ERROR: syntax error at or near "RETURN" LINE 1: ...ll select ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN ^ QUERY: select ppr_pf_inn_verdi( $1 , $2 , $3 , $4 , $5 ) union all select ppr_pf_inn_antall( $1 , $2 , $3 , $4 , $5 ) RETURN CONTEXT: SQL statement in PL/PgSQL function "ppr_test_to_funk" near line 7 CREATE OR REPLACE FUNCTION ppr_test_to_funk(aarstall int, frauke int, tiluke int, prosjektkode int[], teamkode int[]) RETURNS setof integer AS $BODY$ I called the function with this: select * from ppr_test_to_funk(2011,1,52,array[3], array[7,4]) Am I lost or are this possible? *Updated function:* BEGIN return query select ppr_pf_inn_verdi($1,$2,$3,$4,$5) union all select ppr_pf_inn_antall($1,$2,$3,$4,$5) RETURN; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION ppr_test_to_funk() OWNER TO oystein; -- View this message in context: http://postgresql.1045698.n5.nabble.com/Returning-data-from-multiple-functions-tp4980747p4980786.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
