Re: Scaler forms as function arguments

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