Обсуждение: Functions with dynamic queries
Hi, I would appreciate it if somebody could send me some examples (or url) on how to create functions with text parameters, use these parameters to build a dynamic query and return a value obtained from that query. A very simple case would be: Create table people (nif char(12),name varchar(40),primary (nif) ); And now to create a function to return "name" value giving "nif" values as a parameter. Thanks -- Gabriel D.
I guess the examples on standard docs explain it. dont they? > > A very simple case would be: > > Create table people ( > nif char(12), > name varchar(40), > primary (nif) > ); > > And now to create a function to return "name" value giving > "nif" values as a parameter. you may not need a function at all if your case is that simple . you can use subselects and || operators to do many complicated things.. can we know a bit more abt. your specific problem regds ,-- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
> you may not need a function at all if your case is that simple . you
> can use subselects and || operators to do many complicated things..
> can we know a bit more abt. your specific problem
Ok. I have this query:
Select sum(stocks.stkreal)::text || ',' || sum(stocks.stkpteser)::text as stock
From stocks, prendas
Where prendas.codprenda = stocks.codprenda and
prendas.codarticulo = '020720004';
And I want to create a function which receives
"codarticulo" as a parameter.
*----------------
Create Function calc_stocks(text) Returns text AS '
Declare codart ALIAS For $1; all_stocks record;
BeginExecute '' Select sum(stocks.stkreal)::text || '' || '''''' ,'''''' || '' || '' || ''sum(stocks.stkpteser)::text
asstock '' || ''into all_stocks '' || ''From stocks, prendas '' || ''Where prendas.codprenda = stocks.codprenda and ''
||''prendas.codarticulo = codart; '' Return all_stocks.stock;
End;
' language 'plpgsql';
*-----------------
gesyweb=# select calc_stocks('020220064');
ERROR: record all_stocks is unassigned yet
gesyweb=#
Try this instead:
Create Function calc_stocks(text) Returns text AS '
Declare codart ALIAS For $1; all_stocks record; stock text;
Begin
Select sum stocks.stkreal as stock1, sum stocks.stkpteser) as stock2
into all_stocks
From stocks, prendas
Where prendas.codprenda = stocks.codprenda and prendas.codarticulo = codart;
stock := (all_stocks.stock1 || all_stocks.stock2)::text; Return stock;
End;
' language 'plpgsql';
--- Gabriel Dovalo Carril <dovalo@terra.es> wrote:
>
>
> > you may not need a function at all if your case is
> that simple . you
> > can use subselects and || operators to do many
> complicated things..
> > can we know a bit more abt. your specific problem
>
>
> Ok. I have this query:
>
> Select sum(stocks.stkreal)::text || ',' ||
> sum(stocks.stkpteser)::text as stock
> From stocks, prendas
> Where prendas.codprenda = stocks.codprenda and
> prendas.codarticulo = '020720004';
>
> And I want to create a function which receives
> "codarticulo" as a parameter.
>
> *----------------
> Create Function calc_stocks(text) Returns text AS '
> Declare
> codart ALIAS For $1;
> all_stocks record;
> Begin
> Execute '' Select sum(stocks.stkreal)::text || ''
> || '''''' ,''''''
> || '' || ''
> || ''sum(stocks.stkpteser)::text as stock ''
> || ''into all_stocks ''
> || ''From stocks, prendas ''
> || ''Where prendas.codprenda = stocks.codprenda
> and ''
> || ''prendas.codarticulo = codart; ''
> Return all_stocks.stock;
> End;
> ' language 'plpgsql';
> *-----------------
> gesyweb=# select calc_stocks('020220064');
> ERROR: record all_stocks is unassigned yet
> gesyweb=#
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
Reminder to self: cast to text _before_
concatenating(!)
I won't retype the code, but I hope you get the
idea...
--- Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
> Try this instead:
>
> Create Function calc_stocks(text) Returns text AS '
> Declare
> codart ALIAS For $1;
> all_stocks record;
> stock text;
> Begin
> Select sum stocks.stkreal as stock1,
> sum stocks.stkpteser) as stock2
> into all_stocks
> From stocks, prendas
> Where prendas.codprenda = stocks.codprenda and
> prendas.codarticulo = codart;
> stock := (all_stocks.stock1 ||
> all_stocks.stock2)::text;
> Return stock;
> End;
> ' language 'plpgsql';
>
> --- Gabriel Dovalo Carril <dovalo@terra.es> wrote:
> >
> >
> > > you may not need a function at all if your case
> is
> > that simple . you
> > > can use subselects and || operators to do many
> > complicated things..
> > > can we know a bit more abt. your specific
> problem
> >
> >
> > Ok. I have this query:
> >
> > Select sum(stocks.stkreal)::text || ',' ||
> > sum(stocks.stkpteser)::text as stock
> > From stocks, prendas
> > Where prendas.codprenda = stocks.codprenda and
> > prendas.codarticulo = '020720004';
> >
> > And I want to create a function which receives
> > "codarticulo" as a parameter.
> >
> > *----------------
> > Create Function calc_stocks(text) Returns text AS
> '
> > Declare
> > codart ALIAS For $1;
> > all_stocks record;
> > Begin
> > Execute '' Select sum(stocks.stkreal)::text || ''
> > || '''''' ,''''''
> > || '' || ''
> > || ''sum(stocks.stkpteser)::text as stock ''
> > || ''into all_stocks ''
> > || ''From stocks, prendas ''
> > || ''Where prendas.codprenda = stocks.codprenda
> > and ''
> > || ''prendas.codarticulo = codart; ''
> > Return all_stocks.stock;
> > End;
> > ' language 'plpgsql';
> > *-----------------
> > gesyweb=# select calc_stocks('020220064');
> > ERROR: record all_stocks is unassigned yet
> > gesyweb=#
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> __________________________________________________
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
Jeff Eckermann escribió:
>
> Reminder to self: cast to text _before_
> concatenating(!)
> I won't retype the code, but I hope you get the
> idea...
Yes, I have tried this:
Create Function calc_stocks(text) Returns text AS '
Declare codart ALIAS For $1; all_stocks record; stock text;
Begin
Select sum(stocks.stkreal) as stock1, sum(stocks.stkpteser) as stock2 into all_stocksFrom stocks, prendasWhere
prendas.codprenda= stocks.codprenda and prendas.codarticulo = codart;
stock := (all_stocks.stock1::text || all_stocks.stock2::text); Return stock;
End;
' language 'plpgsql';
But now I have got neither error nor
result.
gesyweb=# select calc_stocks('020220064');calc_stocks
-------------
(1
row)
--
Gabriel D.
"Rajesh Kumar Mallah." escribió: > > I guess the examples on standard docs explain it. > dont they? > Hi Rajesh, I have read docs examples again, and even examples at: http://www.brasileiro.net/postgres/plpgsql/plpgsql-description.html section 1.2.5.3, which explains how to execute dynamic queries. At the end of this section there is an example with a comment: -- This works because we are not substituting any variables -- Otherwise is would fail. And I need to know how can a query be executed substituting variables. Thanks, -- Gabriel D.
Hi Gabriel, I am not very adept in using PLPGSQL. Once I had tried to create a query dynamically and execute it but did not work out (but i do not exactly remeber the problem). but what i feel is using "EXECUTE" would be a overkill. I think Jeff has almost solved your problem and with your little coperation the problem can be sorted out. If you still really need the solution desperately i would apprecite if you could prepare an .sql file which creates , the table and inserts some data relevent to the SQL query into it , attach it and post to the list. Regds Mallah. > And I need to know how can a query be executed substituting > variables. > On Friday 24 May 2002 01:54 pm, Gabriel Dovalo Carril wrote: > "Rajesh Kumar Mallah." escribió: > > I guess the examples on standard docs explain it. > > dont they? > > Hi Rajesh, > > I have read docs examples again, and even examples at: > > http://www.brasileiro.net/postgres/plpgsql/plpgsql-description.html > > section 1.2.5.3, which explains how to execute dynamic queries. > At the end of this section there is an example with a comment: > > -- This works because we are not substituting any variables > -- Otherwise is would fail. > > And I need to know how can a query be executed substituting > variables. > > Thanks, -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Hmm. The fact that you don't get an error suggests
that the function is running correctly, but not doing
what we expect.
What do you get when you just run the SQL from the
command line?
I am assuming that something is actually returned for
both sums: if there are no values for either "stkreal"
or "stkpteser" for "codarticulo = '020220064'", then a
null will be returned, which will turn the final
result into a null...
If there are null values anywhere in those columns,
then that could happen at any time. To guard against
that, use something like:
stock := coalesce(all_stocks.stock1::text, '') ||
coalesce(all_stocks.stock2::text, '');
--- Gabriel Dovalo Carril <dovalo@terra.es> wrote:
> Jeff Eckermann escribi�:
> >
> > Reminder to self: cast to text _before_
> > concatenating(!)
> > I won't retype the code, but I hope you get the
> > idea...
>
> Yes, I have tried this:
>
> Create Function calc_stocks(text) Returns text AS '
> Declare
> codart ALIAS For $1;
> all_stocks record;
> stock text;
> Begin
> Select sum(stocks.stkreal) as stock1,
> sum(stocks.stkpteser) as stock2
> into all_stocks
> From stocks, prendas
> Where prendas.codprenda = stocks.codprenda and
> prendas.codarticulo = codart;
> stock := (all_stocks.stock1::text ||
> all_stocks.stock2::text);
> Return stock;
> End;
> ' language 'plpgsql';
>
> But now I have got neither error nor
> result.
>
> gesyweb=# select calc_stocks('020220064');
> calc_stocks
> -------------
>
> (1
> row)
>
>
>
> --
> Gabriel D.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
> What do you get when you just run the SQL from the
> command line?
It works fine in command line .
> I am assuming that something is actually returned for
> both sums: if there are no values for either "stkreal"
> or "stkpteser" for "codarticulo = '020220064'", then a
> null will be returned, which will turn the final
> result into a null...
> If there are null values anywhere in those columns,
No, there are no NULL values.
Try attached scripts. They are only examples, (no real data)
They are very, very simple and fails in the same situation.
The only difference between then is line 19
(func_error.sql)
Select people.age into person
(func_nodata.sql)
Select max(people.age) as age into person
The first one returns:
ERROR: record person is unassigned yet
And the second one:
get_age
---------
(1
row)
--
Gabriel Dovalo.
Create table people (
nif char(12),
age int4,
primary key (nif)
);
insert into people (nif, age) values (
'001', 10);
insert into people (nif, age) values (
'002', 30);
Create Function get_age(text) Returns text as '
Declare
param_nif Alias For $1;
person record;
text_to_return text;
Begin
Select people.age into person
From people
Where people.nif = param_nif;
text_to_return := person.age::text;
Return text_to_return;
End;
' language 'plpgsql';
Select get_age('001');
Create table people (
nif char(12),
age int4,
primary key (nif)
);
insert into people (nif, age) values (
'001', 10);
insert into people (nif, age) values (
'002', 30);
Create Function get_age(text) Returns text as '
Declare
param_nif Alias For $1;
person record;
text_to_return text;
Begin
Select max(people.age) as age into person
From people
Where people.nif = param_nif;
text_to_return := person.age::text;
Return text_to_return;
End;
' language 'plpgsql';
Select get_age('001');
Hi Gabriel, sorry for the late response, I tried both the scripts and they do work. psql -h 192.168.0.11 -U tradein test -f b.sql DROP psql:b.sql:6: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'people_pkey' for table 'people' CREATE INSERT 27856939 1 INSERT 27856940 1 DROP CREATEget_age ---------10 (1 row) Hmm, am i missing anything ? mallah. Try attached scripts. They are only examples, (no real data)They are very, very simple and fails in the same situation. > The only difference between then is line 19 > > (func_error.sql) > Select people.age into person > > (func_nodata.sql) > Select max(people.age) as age into person > > > The first one returns: > ERROR: record person is unassigned yet > > And the second one: > get_age > --------- > > (1 > row) -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Rajesh, Thanks for telling me that I am _not_ going crazy. Because I could not see any reason why Gabriel's scripts should not work. Gabriel, I have two remaining suggestions: 1. Maybe you have stuff in your database that you do not expect. Perhaps try running this via a newly-created, empty database? 2. If that still doesn't work, you probably have a problem with your installation. If you post details of your OS platform, PostgreSQL version etc., some guru on the list may be able to suggest something. Sorry, that's the best I can think of. Jeff --- "Rajesh Kumar Mallah." <mallah@trade-india.com> wrote: > > Hi Gabriel, > > sorry for the late response, > > I tried both the scripts and they do work. > > psql -h 192.168.0.11 -U tradein test -f b.sql > DROP > psql:b.sql:6: NOTICE: CREATE TABLE / PRIMARY KEY > will create implicit index 'people_pkey' for table > 'people' > CREATE > INSERT 27856939 1 > INSERT 27856940 1 > DROP > CREATE > get_age > --------- > 10 > (1 row) > > Hmm, am i missing anything ? > > mallah. > > > > > > > > Try attached scripts. They are only examples, (no > real data) > They are very, very simple and fails in the same > situation. > > > The only difference between then is line 19 > > > > (func_error.sql) > > Select people.age into person > > > > (func_nodata.sql) > > Select max(people.age) as age into person > > > > > > The first one returns: > > ERROR: record person is unassigned yet > > > > And the second one: > > get_age > > --------- > > > > (1 > > row) > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com
Jeff Eckermann escribió:
>
> Rajesh,
> Thanks for telling me that I am _not_ going crazy.
> Because I could not see any reason why Gabriel's
> scripts should not work.
> Gabriel, I have two remaining suggestions:
> 1. Maybe you have stuff in your database that you do
> not expect. Perhaps try running this via a
> newly-created, empty database?
> 2. If that still doesn't work, you probably have a
> problem with your installation.
Yes, I have made a new installation, and the
query has worked.
Thanks,
--
Gabriel D.