SQL function and input variables

On 21/09/2011 13:56, Martín Marqués wrote:

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?

No, you can't - that works in pl/pgsql, but not in SQL.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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

Thomas

2011/9/21 Thomas Kellerer <spam_eater@gmx.net>:

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

Thanks for the tip. Didn't know that, and just kept using plpgsql sintax.

Thanks again.

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

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

You can do something like:
DECLARE
p ALIAS FOR $1;

in the function, if you want.

Susan

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martín Marqués
Sent: Wednesday, September 21, 2011 6:09 AM
To: Thomas Kellerer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL function and input variables

2011/9/21 Thomas Kellerer <spam_eater@gmx.net>:

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

Thanks for the tip. Didn't know that, and just kept using plpgsql sintax.

Thanks again.

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general