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