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
>