X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 6FC59D1B432
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Nov 2003 17:25:51 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 76760-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Nov 2003 13:25:26 -0400 (AST)
Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net
	[194.217.242.88])
	by svr1.postgresql.org (Postfix) with ESMTP id 15235D1B431
	for <pgsql-sql@postgresql.org>; Wed, 26 Nov 2003 13:25:20 -0400 (AST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1AP3Pp-000NTi-0U; Wed, 26 Nov 2003 17:25:25 +0000
Received: by mainbox.archonet.com (Postfix, from userid 529)
	id 790D7172A9; Wed, 26 Nov 2003 17:25:24 +0000 (GMT)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 962DA172A7; Wed, 26 Nov 2003 17:25:22 +0000 (GMT)
From: Richard Huxton <dev@archonet.com>
To: Andreas Tille <tillea@rki.de>,
	PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Scaler forms as function arguments
Date: Wed, 26 Nov 2003 17:25:21 +0000
User-Agent: KMail/1.5
References: <Pine.LNX.4.56.0311261255210.17774@wr-linux02.rki.ivbb.bund.de>
In-Reply-To: <Pine.LNX.4.56.0311261255210.17774@wr-linux02.rki.ivbb.bund.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200311261725.21975.dev@archonet.com>
X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/296
X-Sequence-Number: 16111

On Wednesday 26 November 2003 15:40, Andreas Tille wrote:
> Hi,
>
> I want to write a function of the following type
>
>    CREATE FUNCTION test ( <scalar form type> )
>          RETURNS setof MyTable
>          AS
>          'SELECT * FROM MyTable WHERE id IN $1'
>          LANGUAGE 'SQL' ;

Not as you've done it. You could pass in text "(1,2,3)", build your query and 
use EXECUTE to execute it. Alternatively, you might be able to do it with an 
array parameter (sorry, I don't use arrays, so I can't be sure).

> I was not able to find a reference whether this is possible and if yes
> how to specify the argument type and how to call this function to hand over
> the list for the IN clause correctly.
>
> Next question.  Is there any example how to return a SETOF MyTable in
> a plpgsql function?  I tried
>
>  CREATE FUNCTION test2()
>     RETURNS setof MyTable
>     AS '
>     DECLARE
>        result   SETOF MyTable ;
>     BEGIN
>       result := (SELECT * FROM MyTable);
>       RETURN result ;
>     END; ' LANGUAGE 'plpgsql';

Read the section on plpgsql in the manuals, you return results one at a time. 
For some examples, see http://techdocs.postgresql.org/ and look for the "Set 
Returning Functions" item.
-- 
  Richard Huxton
  Archonet Ltd