Обсуждение: 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?

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?
>
>
>

"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

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
>
>

> 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