About a PL/pgSQL function

From: Ferdinand Smit <ferdinand(at)telegraafnet(dot)nl>
To: pgsql-admin(at)postgresql(dot)org
Subject: About a PL/pgSQL function
Date: 2002-06-06 15:38:51
Message-ID: 200206061738.51230.ferdinand@telegraafnet.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I've created a little function, that returns an id and creates one if it does
not exist.

DROP FUNCTION fn(text,text);
CREATE OR REPLACE FUNCTION fn(text,text) RETURNS INT AS '
DECLARE
record_id INTEGER;
BEGIN

SELECT "id" INTO record_id FROM $1 WHERE def = $2 ;

IF NOT FOUND THEN
INSERT INTO $1 (def) VALUES($2);
SELECT "id" INTO record_id FROM $1 WHERE def = $2;
END IF;

RETURN record_id;
END;
' LANGUAGE 'plpgsql';

When i run it i get:
NOTICE: Error occurred while executing PL/pgSQL function fn
NOTICE: line 5 at select into variables
ERROR: parser: parse error at or near "$1"

When i create a function with a "static" table name, it works fine.
EXECUTE does not allow SELECT INTO, so does anyone have an other solution?

Ferdinand

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Oliver Elphick 2002-06-06 16:14:05 Re: [GENERAL] performance issue using DBI
Previous Message Joshua b. Jore 2002-06-06 13:55:40 Re: [GENERAL] performance issue using DBI