much thanks for the earlier help.

Sincerely

Richard Sydney-Smith

------=_NextPart_000_0012_01C37919.441B9EF0-- From sszabo@megazone.bigpanda.com Fri Sep 12 02:46:59 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7C2D3D1B533 for ; Fri, 12 Sep 2003 02:46:59 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 65003-09 for ; Thu, 11 Sep 2003 23:46:31 -0300 (ADT) Received: from megazone.bigpanda.com (45.mufb.snfc.sffca01r18.dsl.att.net [12.103.249.45]) by svr1.postgresql.org (Postfix) with ESMTP id 5D3E0D1B500 for ; Thu, 11 Sep 2003 23:46:27 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id C0206D65D; Thu, 11 Sep 2003 19:46:28 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id B5AF55B9D; Thu, 11 Sep 2003 19:46:28 -0700 (PDT) Date: Thu, 11 Sep 2003 19:46:28 -0700 (PDT) From: Stephan Szabo To: Richard Sydney-Smith Cc: Subject: Re: How do I replace select ... into commands? In-Reply-To: <001501c378d6$3aa78f20$df6c32d2@athlon2000> Message-ID: <20030911194512.Q47864-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/90 X-Sequence-Number: 15068 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? From richard@ibisaustralia.com Fri Sep 12 02:57:25 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 77631D1B500 for ; Fri, 12 Sep 2003 02:57:25 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 74121-06 for ; Thu, 11 Sep 2003 23:57:18 -0300 (ADT) Received: from server146.hostingrevolution.com (server146.hostingrevolution.com [64.21.143.245]) by svr1.postgresql.org (Postfix) with ESMTP id 8D2A1D1B4E6 for ; Thu, 11 Sep 2003 23:57:14 -0300 (ADT) Received: from 223.a.002.pth.iprimus.net.au ([210.50.108.223] helo=athlon2000) by server146.hostingrevolution.com with smtp (Exim 4.22) id 19xe7b-00089J-C3; Thu, 11 Sep 2003 21:56:58 -0500 Message-ID: <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000> From: "Richard Sydney-Smith" To: "Stephan Szabo" Cc: References: <20030911194512.Q47864-100000@megazone.bigpanda.com> Subject: Re: How do I replace select ... into commands? Date: Fri, 12 Sep 2003 10:59:08 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server146.hostingrevolution.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - ibisaustralia.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/91 X-Sequence-Number: 15069 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" To: "Richard Sydney-Smith" Cc: 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? > > > From tgl@sss.pgh.pa.us Fri Sep 12 03:20:33 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A0CF1D1B533 for ; Fri, 12 Sep 2003 03:20:33 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 74429-09 for ; Fri, 12 Sep 2003 00:20:23 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 9DB0DD1B4E2 for ; Fri, 12 Sep 2003 00:20:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h8C3K7An006096; Thu, 11 Sep 2003 23:20:08 -0400 (EDT) To: "Richard Sydney-Smith" Cc: "Stephan Szabo" , pgsql-sql@postgresql.org Subject: Re: How do I replace select ... into commands? In-reply-to: <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000> References: <20030911194512.Q47864-100000@megazone.bigpanda.com> <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000> Comments: In-reply-to "Richard Sydney-Smith" message dated "Fri, 12 Sep 2003 10:59:08 +0800" Date: Thu, 11 Sep 2003 23:20:07 -0400 Message-ID: <6095.1063336807@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/92 X-Sequence-Number: 15070 "Richard Sydney-Smith" 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 From richard@ibisaustralia.com Fri Sep 12 05:05:23 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A706BD1B519 for ; Fri, 12 Sep 2003 05:05:23 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 00819-02 for ; Fri, 12 Sep 2003 02:05:13 -0300 (ADT) Received: from server146.hostingrevolution.com (server146.hostingrevolution.com [64.21.143.245]) by svr1.postgresql.org (Postfix) with ESMTP id 7CFCED1B504 for ; Fri, 12 Sep 2003 02:05:12 -0300 (ADT) Received: from 223.a.002.pth.iprimus.net.au ([210.50.108.223] helo=athlon2000) by server146.hostingrevolution.com with smtp (Exim 4.22) id 19xg7J-0001yo-Qs; Fri, 12 Sep 2003 00:04:48 -0500 Message-ID: <002f01c378eb$b3fdaca0$df6c32d2@athlon2000> From: "Richard Sydney-Smith" To: "Tom Lane" Cc: References: <20030911194512.Q47864-100000@megazone.bigpanda.com> <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000> <6095.1063336807@sss.pgh.pa.us> Subject: Re: How do I replace select ... into commands? Date: Fri, 12 Sep 2003 13:06:57 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server146.hostingrevolution.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - ibisaustralia.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/93 X-Sequence-Number: 15071 Thanks that fixed it. .... the more I use it the happier I get with postgresql. Best Regards Richard ----- Original Message ----- From: "Tom Lane" To: "Richard Sydney-Smith" Cc: "Stephan Szabo" ; Sent: Friday, September 12, 2003 11:20 AM Subject: Re: [SQL] How do I replace select ... into commands? > "Richard Sydney-Smith" 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 > > From dev@archonet.com Fri Sep 12 07:08:54 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id B782CD1B4E5 for ; Fri, 12 Sep 2003 07:08:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 17803-08 for ; Fri, 12 Sep 2003 04:08:25 -0300 (ADT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id C7A07D1B51A for ; Fri, 12 Sep 2003 04:08:23 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1) id 19xi2Z-000EZj-0Z; Fri, 12 Sep 2003 08:08:24 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 9DE3B168E3; Fri, 12 Sep 2003 08:08:22 +0100 (BST) Received: from mainbox.archonet.com (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with SMTP id EC87F1648F; Fri, 12 Sep 2003 08:08:21 +0100 (BST) Received: from 192.168.1.32 (SquirrelMail authenticated user dev) by mainbox.archonet.com with HTTP; Fri, 12 Sep 2003 08:08:22 +0100 (BST) Message-ID: <49234.192.168.1.32.1063350502.squirrel@mainbox.archonet.com> In-Reply-To: <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000> References: <20030911194512.Q47864-100000@megazone.bigpanda.com> <001d01c378d9$d8a8bfc0$df6c32d2@athlon2000> Date: Fri, 12 Sep 2003 08:08:22 +0100 (BST) Subject: Re: How do I replace select ... into commands? From: "Richard Huxton" To: "Richard Sydney-Smith" Cc: "Stephan Szabo" , pgsql-sql@postgresql.org User-Agent: SquirrelMail/1.4.0 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/95 X-Sequence-Number: 15073 > 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