Thread: Newbie in functions

I´m trying to create a function to obtain the possition of a substring, but
i don't kown if is correct
can anyone can  help me to do this?

test=# CREATE FUNCTION pos1 (int4) RETURNS int4 AS '
test'# UPDATE tabla1
test'#   SET posicion1 = position('pm' in SELECT string1 FROM tabla1 WHERE
id = $1)
test'#   WHERE id = $1;
test'# SELECT posicion1 FROM tabla1 WHERE id = $1;
test'# 'LANGUAGE sql;
ERROR:  parser: parse error at or near "pm"
test=#


thanks for your help

Hi,

>I´m trying to create a function to obtain the possition of a substring, but
>i don't kown if is correct
>can anyone can  help me to do this?
>
>test=# CREATE FUNCTION pos1 (int4) RETURNS int4 AS '
>test'# UPDATE tabla1
>test'#   SET posicion1 = position('pm' in SELECT string1 FROM tabla1 WHERE
>id = $1)
>test'#   WHERE id = $1;
>test'# SELECT posicion1 FROM tabla1 WHERE id = $1;
>test'# 'LANGUAGE sql;
>ERROR:  parser: parse error at or near "pm"
>test=#

This one's a fairly simple one. You need to escape the quote around pm, so
it turns into:

SET posicion1 = position(\'pm\' in SELECT string1 FROM tabla1 WHERE
.........

(This is because you're already inside the function which starts at the
first ' on the top line).

.. and you're not returning any values yet either :)

HTH,

-----------------
      Chris Smith
http://www.squiz.net/

Put single quote twice ( '' ) before and after "pm"....

Denis

----- Original Message -----
From: <Eduardo_Cadena@praxair.com>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, April 17, 2002 8:22 PM
Subject: [NOVICE] Newbie in functions


> I´m trying to create a function to obtain the possition of a substring,
but
> i don't kown if is correct
> can anyone can  help me to do this?
>
> test=# CREATE FUNCTION pos1 (int4) RETURNS int4 AS '
> test'# UPDATE tabla1
> test'#   SET posicion1 = position('pm' in SELECT string1 FROM tabla1 WHERE
> id = $1)
> test'#   WHERE id = $1;
> test'# SELECT posicion1 FROM tabla1 WHERE id = $1;
> test'# 'LANGUAGE sql;
> ERROR:  parser: parse error at or near "pm"
> test=#
>
>
> thanks for your help
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)