Thread: Parsing error

Hi every one

I have a big problem, I think it's a syntaxical one, but can't solve it,
please help !

What I am trying to do is a simple function who updates data in a table and
if no row was updated, then add a new one with the specified parameters.
And i would like to do it using SQL and anything else.

Below is the screen shot from psql (I run postgresql-7.1.2-4PGDG )

[~/pgsql/test]$ psql ze_database ze_user
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

ze_database=> CREATE TABLE my_table (
ze_database(> p1 integer,
ze_database(> p2 integer,
ze_database(> p3 integer,
ze_database(> p4 integer);
CREATE
ze_database=> CREATE FUNCTION my_add(integer,integer,integer,integer)
ze_database-> RETURNS integer
ze_database-> AS 'INSERT INTO my_table VALUES ($1,$2,$3,$4); SELECT 1'
ze_database-> LANGUAGE 'sql';
CREATE
ze_database=> CREATE FUNCTION my_update(integer,integer,integer,integer)
ze_database-> RETURNS integer
ze_database-> AS 'SELECT CASE WHEN UPDATE my_table SET p1= $1, p2= $2 WHERE
p3= $3 AND p4= $4 ~* \'UPDATE 0\' THEN my_add($1,$2,$3,$4) ELSE 1 END'
ze_database-> LANGUAGE 'sql';
ERROR:  parser: parse error at or near "my_table"
ze_database=>

I don't see my error, can anyone open my eyes and correct me, please help

Thanks in advance

> ze_database=> CREATE TABLE my_table (
> ze_database(> p1 integer,
> ze_database(> p2 integer,
> ze_database(> p3 integer,
> ze_database(> p4 integer);
> CREATE
> ze_database=> CREATE FUNCTION my_add(integer,integer,integer,integer)
> ze_database-> RETURNS integer
> ze_database-> AS 'INSERT INTO my_table VALUES ($1,$2,$3,$4); SELECT 1'
> ze_database-> LANGUAGE 'sql';
> CREATE
> ze_database=> CREATE FUNCTION my_update(integer,integer,integer,integer)
> ze_database-> RETURNS integer
> ze_database-> AS 'SELECT CASE WHEN UPDATE my_table SET p1= $1, p2= $2 WHERE
> p3= $3 AND p4= $4 ~* \'UPDATE 0\' THEN my_add($1,$2,$3,$4) ELSE 1 END'
> ze_database-> LANGUAGE 'sql';
> ERROR:  parser: parse error at or near "my_table"
> ze_database=>

You are not allowed to have an SQL query in a CASE clause. The condition after WHEN must be an expression that returns
aboolean. You would be better off using plpgsql for something like what you want. 


cheers,
thalis