Re: SQL function and input variables

Thomas Kellerer <spam_eater@gmx.net> writes:
> Martín Marqués, 21.09.2011 14:56:
>> I was makeing an SQL function and got an error which on a sintax that
>> I thouhgt would work:
>>
>> CREATE OR REPLACE FUNCTION dicInsertarPalabra(p TEXT)
>> RETURNS INT AS $body$
>> INSERT INTO public.diccionario (palabra) VALUES (quote_literal(p));
>> SELECT COALESCE(codigo,0) FROM public.diccionario
>> WHERE palabra = quote_literal(p);
>> $body$ LANGUAGE 'SQL';
>>
>> Changing p for $1 in the body of the function makes it work. But,
>> can't we label input arguments like how I did here?

> This is because the language SQL does not support named parameters, only positional ones.
> http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-NAMED-PARAMETERS

There was some discussion of fixing that, not too long ago, but (IIRC)
we couldn't come to a consensus on what to do with ambiguous cases,
where for example "p" is also the name of a column available from one
of the tables in the query.

            regards, tom lane