pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions with dynamic queries
13+ messages / 3 participants
[nested] [flat]
* Functions with dynamic queries
@ 2002-05-23 10:34 Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 13+ messages in thread
From: Gabriel Dovalo Carril @ 2002-05-23 10:34 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
@ 2002-05-23 13:44 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 08:24 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
0 siblings, 2 replies; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-05-23 13:44 UTC (permalink / raw)
To: Gabriel Dovalo Carril <dovalo@terra.es>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-05-23 20:48 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
1 sibling, 1 reply; 13+ messages in thread
From: Gabriel Dovalo Carril @ 2002-05-23 20:48 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
> 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=#
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
@ 2002-05-23 22:40 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Jeff Eckermann @ 2002-05-23 22:40 UTC (permalink / raw)
To: Gabriel Dovalo Carril <dovalo@terra.es>; Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2002-05-23 22:43 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 23:37 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
0 siblings, 1 reply; 13+ messages in thread
From: Jeff Eckermann @ 2002-05-23 22:43 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; Gabriel Dovalo Carril <dovalo@terra.es>; Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2002-05-23 23:37 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 13:51 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Gabriel Dovalo Carril @ 2002-05-23 23:37 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 23:37 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
@ 2002-05-24 13:51 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-25 01:11 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
0 siblings, 1 reply; 13+ messages in thread
From: Jeff Eckermann @ 2002-05-24 13:51 UTC (permalink / raw)
To: Gabriel Dovalo Carril <dovalo@terra.es>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 23:37 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 13:51 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2002-05-25 01:11 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-26 10:25 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 13+ messages in thread
From: Gabriel Dovalo Carril @ 2002-05-25 01:11 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: pgsql-sql
> 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.
Attachments:
func_error.sql (text/plain, 516B) download
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');
func_nodata.sql (text/plain, 528B) download
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');
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 23:37 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 13:51 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-25 01:11 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
@ 2002-05-26 10:25 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-28 17:04 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-05-26 10:25 UTC (permalink / raw)
To: Gabriel Dovalo Carril <dovalo@terra.es>; Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 23:37 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 13:51 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-25 01:11 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-26 10:25 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-05-28 17:04 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-28 18:24 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
0 siblings, 1 reply; 13+ messages in thread
From: Jeff Eckermann @ 2002-05-28 17:04 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; Gabriel Dovalo Carril <dovalo@terra.es>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 23:37 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 13:51 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-25 01:11 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-26 10:25 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-28 17:04 ` Re: Functions with dynamic queries Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2002-05-28 18:24 ` Gabriel Dovalo Carril <dovalo@terra.es>
0 siblings, 0 replies; 13+ messages in thread
From: Gabriel Dovalo Carril @ 2002-05-28 18:24 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2002-05-24 08:24 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 11:53 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 1 reply; 13+ messages in thread
From: Gabriel Dovalo Carril @ 2002-05-24 08:24 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
"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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Functions with dynamic queries
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Re: Functions with dynamic queries Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-24 08:24 ` Re: Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
@ 2002-05-24 11:53 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-05-24 11:53 UTC (permalink / raw)
To: Gabriel Dovalo Carril <dovalo@terra.es>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2002-05-28 18:24 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-23 10:34 Functions with dynamic queries Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 13:44 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-23 20:48 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-23 22:40 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 22:43 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-23 23:37 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 13:51 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-25 01:11 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-26 10:25 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-05-28 17:04 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-05-28 18:24 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 08:24 ` Gabriel Dovalo Carril <dovalo@terra.es>
2002-05-24 11:53 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
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