From: Joe Conway <mail@joeconway.com>
To: Richard Huxton <dev@archonet.com>
Cc: Andreas Tille <tillea@rki.de>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Scaler forms as function arguments
Date: Thu, 27 Nov 2003 21:47:10 -0800
Message-ID: <3FC6E15E.2030300@joeconway.com> (raw)
In-Reply-To: <200311261725.21975.dev@archonet.com>
References: <Pine.LNX.4.56.0311261255210.17774@wr-linux02.rki.ivbb.bund.de>
<200311261725.21975.dev@archonet.com>
Richard Huxton wrote:
> On Wednesday 26 November 2003 15:40, Andreas Tille wrote:
>>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).
In 7.4 you could use an array. It would look like this:
CREATE TABLE mytable (id int, idval text);
INSERT INTO mytable VALUES (1,'a');
INSERT INTO mytable VALUES (2,'b');
INSERT INTO mytable VALUES (3,'c');
CREATE FUNCTION test (int[]) RETURNS setof MyTable AS '
SELECT * FROM mytable WHERE id = ANY ($1)
' LANGUAGE 'SQL' ;
regression=# SELECT * FROM test(ARRAY[1,3]);
id | idval
----+-------
1 | a
3 | c
(2 rows)
HTH,
Joe
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: mail@joeconway.com, dev@archonet.com, tillea@rki.de
Subject: Re: Scaler forms as function arguments
In-Reply-To: <3FC6E15E.2030300@joeconway.com>
* 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