Stored procedure doesn't return expected result.

Stuart Grimshaw <stuart.grimshaw@gmail.com> schrieb:

I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.

create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';
$$ LANGUAGE SQL;

However, when I do this:

select get_client_id('Stuart Grimshaw');

I get no results, yet:

Please read our documentation about executing dynamic commands:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Stuart Grimshaw <stuart.grimshaw@gmail.com> schrieb:

I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.

create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';

^ ^

remove the '

test=# select * from foo1;
x | i
---+---
a | 1
b | 2
(2 rows)

test=# create or replace function get_i(varchar) returns int as $$
select i from foo1 where x = $1;$$ language sql;
CREATE FUNCTION
test=# select get_i('a');
get_i
-------
1
(1 row)

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On 2/26/06, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Stuart Grimshaw <stuart.grimshaw@gmail.com> schrieb:

I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.

create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';

^ ^

remove the '

test=# select * from foo1;
x | i
---+---
a | 1
b | 2
(2 rows)

test=# create or replace function get_i(varchar) returns int as $$
select i from foo1 where x = $1;$$ language sql;
CREATE FUNCTION
test=# select get_i('a');
get_i
-------
1
(1 row)

That's got it. Obviously it understands that $1 is a string and not a
column name.

Thanks very much.

--
-S
http://www.makepovertyhistory.org/