From: Richard Sydney-Smith <richard@ibisaustralia.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How do I replace select ... into commands?
Date: Fri, 12 Sep 2003 10:59:08 +0800
Message-ID: <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000> (raw)
References: <20030911194512.Q47864-100000@megazone.bigpanda.com>
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?
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@ibisaustralia.com, sszabo@megazone.bigpanda.com
Subject: Re: How do I replace select ... into commands?
In-Reply-To: <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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