pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I replace select ... into commands?
6+ messages / 4 participants
[nested] [flat]
* How do I replace select ... into commands?
@ 2003-09-12 02:33 Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 02:46 ` Re: How do I replace select ... into commands? Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Sydney-Smith @ 2003-09-12 02:33 UTC (permalink / raw)
To: pgsql-sql
I have a number of sybase procedures in which I use something like
declare
mx : integer;
begin
select max(field) from table into mx;
return mx;
end;
Postgresql is telling me that select...into has not been implemented yet. what is the best way to perform this action with the commands offered in plpgsql?
much thanks for the earlier help.
Sincerely
Richard Sydney-Smith
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I replace select ... into commands?
2003-09-12 02:33 How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
@ 2003-09-12 02:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-12 02:59 ` Re: How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-09-12 02:46 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; +Cc: pgsql-sql
On Fri, 12 Sep 2003, Richard Sydney-Smith wrote:
> I have a number of sybase procedures in which I use something like
>
> declare
> mx : integer;
> begin
> select max(field) from table into mx;
> return mx;
> end;
>
> Postgresql is telling me that select...into has not been implemented
> yet. what is the best way to perform this action with the commands
> offered in plpgsql?
Something like the above should work, what's the exact function and error
message and what version are you using?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I replace select ... into commands?
2003-09-12 02:33 How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 02:46 ` Re: How do I replace select ... into commands? Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-09-12 02:59 ` Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 03:20 ` Re: How do I replace select ... into commands? Tom Lane <tgl@sss.pgh.pa.us>
2003-09-12 07:08 ` Re: How do I replace select ... into commands? Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 6+ messages in thread
From: Richard Sydney-Smith @ 2003-09-12 02:59 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
Stephen replied :
> Something like the above should work, what's the exact function and error
> message and what version are you using?
>
>
thanks Stephen.
Exact function definition follows:
PG Version is 7.3.1 on Windows 2000
This procedure allocates unique record number to a number of tables. In the
application it is important that I know the records ID number before it is
inserted into the database. Also use to assign batch and session numbers
etc.
CREATE FUNCTION public.make_rsn(bpchar, bpchar, int4) RETURNS int4 AS '
declare
tbl alias for $1;
seq_fld alias for $2;
incr alias for $3;
rsn integer := 0;
lastrsn integer := 0;
mx integer := 0;
begin
-- look for existing last RSN
select "max"(seq_val) as m into lastrsn from fseqkeys where seq_key = tbl;
if lastrsn=0 or lastrsn is null then
-- no pre existing RSN so we have to search the table
if tbl=''BATCH'' or position(''-'' in tbl)>0 then
mx := 100;
else
execute ''select max('' || seq_fld || '') into mx from '' || tbl;
end if;
rsn := mx+1;
-- dont allow rsn < 100
if rsn<100 or rsn is null then
rsn := 100;
end if;
lastrsn := rsn;
-- record the new rsn
insert into fseqkeys (seq_key,seq_val) values (tbl,rsn);
end if;
-- reserve the required number of rows
rsn := lastrsn+incr;
--and update the fseqkeys table with the RSN number/s we have just used
update fseqkeys set seq_val = rsn where seq_key=tbl;
-- now return the RSN number to the user
return rsn;
end;
' LANGUAGE 'plpgsql' VOLATILE;
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, September 12, 2003 10:46 AM
Subject: Re: [SQL] How do I replace select ... into commands?
> On Fri, 12 Sep 2003, Richard Sydney-Smith wrote:
>
> > I have a number of sybase procedures in which I use something like
> >
> > declare
> > mx : integer;
> > begin
> > select max(field) from table into mx;
> > return mx;
> > end;
> >
> > Postgresql is telling me that select...into has not been implemented
> > yet. what is the best way to perform this action with the commands
> > offered in plpgsql?
>
> Something like the above should work, what's the exact function and error
> message and what version are you using?
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I replace select ... into commands?
2003-09-12 02:33 How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 02:46 ` Re: How do I replace select ... into commands? Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-12 02:59 ` Re: How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
@ 2003-09-12 03:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-12 05:06 ` Re: How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-09-12 03:20 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
"Richard Sydney-Smith" <richard@ibisaustralia.com> writes:
> execute ''select max('' || seq_fld || '') into mx from '' || tbl;
You can't use INTO in an EXECUTE'd select (basically because the string
to be executed is not processed by plpgsql at all, merely sent down to
the SQL engine, which does not know the output variable mx). The way to
get results out of an executed select is to use FOR ... IN EXECUTE.
Which is a kluge, but it holds the fort until someone gets around to
redesigning this code. See the manual.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I replace select ... into commands?
2003-09-12 02:33 How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 02:46 ` Re: How do I replace select ... into commands? Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-12 02:59 ` Re: How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 03:20 ` Re: How do I replace select ... into commands? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-09-12 05:06 ` Richard Sydney-Smith <richard@ibisaustralia.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Sydney-Smith @ 2003-09-12 05:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thanks that fixed it.
.... the more I use it the happier I get with postgresql.
Best Regards
Richard
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>
Cc: "Stephan Szabo" <sszabo@megazone.bigpanda.com>;
<pgsql-sql@postgresql.org>
Sent: Friday, September 12, 2003 11:20 AM
Subject: Re: [SQL] How do I replace select ... into commands?
> "Richard Sydney-Smith" <richard@ibisaustralia.com> writes:
> > execute ''select max('' || seq_fld || '') into mx from '' || tbl;
>
> You can't use INTO in an EXECUTE'd select (basically because the string
> to be executed is not processed by plpgsql at all, merely sent down to
> the SQL engine, which does not know the output variable mx). The way to
> get results out of an executed select is to use FOR ... IN EXECUTE.
> Which is a kluge, but it holds the fort until someone gets around to
> redesigning this code. See the manual.
>
> regards, tom lane
>
> ---------------------------(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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do I replace select ... into commands?
2003-09-12 02:33 How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 02:46 ` Re: How do I replace select ... into commands? Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-12 02:59 ` Re: How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
@ 2003-09-12 07:08 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2003-09-12 07:08 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
> Stephen replied :
>
>> Something like the above should work, what's the exact function and
>> error
>> message and what version are you using?
>>
>>
> thanks Stephen.
>
> Exact function definition follows:
> PG Version is 7.3.1 on Windows 2000
> select "max"(seq_val) as m into lastrsn from fseqkeys where seq_key = tbl;
Slight change of syntax needed:
select into recordvar max(seq_val) from ...
- Richard Huxton
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-09-12 07:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-12 02:33 How do I replace select ... into commands? Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 02:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-12 02:59 ` Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 03:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-12 05:06 ` Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-12 07:08 ` Richard Huxton <dev@archonet.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