Re: SQL function problem in 7.1?

You don't want to quote the $1.  Otherwise you're going to probably
get the literal '$1'.

create function foob(text) returns int as 'select a from foo
where b = $1;' language'sql';

(the space before the $ is significant)

Stephan Szabo
sszabo@bigpanda.com

On Fri, 9 Feb 2001, Richard Huxton wrote:

> Am I doing something stupid with strings in SQL functions.
>
>
> richardh=> select version();
>                                 version
> ------------------------------------------------------------------------
>  PostgreSQL 7.1beta3 on i586-pc-linux-gnu, compiled by GCC egcs-2.91.66
> (1 row)
>
> richardh=> select * from foo;
>  a |  b
> ---+-----
>  1 | aaa
>  2 | bbb
>  3 | ccc
> (3 rows)
>
> richardh=> create function fooa(int) returns text as 'select b from foo
> where a = $1;' language 'sql';
> CREATE
> richardh=> select fooa(2);
>  fooa
> ------
>  bbb
> (1 row)
>
> richardh=> create function foob(text) returns int as 'select a from foo
> where b = ''$1'';' language 'sql';
> CREATE
> richardh=> select foob('bbb');
>  foob
> ------
>
> (1 row)
>
>
> TIA
>
> - Richard Huxton
>