About a PL/pgSQL function
About a PL/pgSQL function
От:
Ferdinand Smit <ferdinand@telegraafnet.nl>
Дата:
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
Re: About a PL/pgSQL function
От:
Joel Burton <joel@joelburton.com>
Дата:
On Thu, 6 Jun 2002, Ferdinand Smit wrote: > 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? Use: DECLARE cursorname refcursor; varname int; BEGIN OPEN cursorname FOR EXECUTE ''SELECT ... FROM '' || $1 || ...; FETCH cursorname INTO varname; CLOSE cursorname; RETURN varname; END; -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant