From: | robert(dot)goodwin(at)ums(dot)msfc(dot)nasa(dot)gov (Robert Goodwin) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Dynamic SQL in function |
Date: | 2002-03-05 16:46:49 |
Message-ID: | 222613cc.0203050846.3e491656@posting.google.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to define a simple function that uses dynamic SQL. Here is
the function definition:
CREATE OR REPLACE FUNCTION count_records (VARCHAR)
RETURN INTEGER AS '
DECLARE
cursor1 REFCURSOR;
num_recs INTEGER;
BEGIN
OPEN cursor1 FOR EXECUTE "SELECT count(*) FROM " ||
quote_ident($1);
FETCH cursor1 INTO num_recs;
RETURN num_recs;
END;
' LANGUAGE 'plpgsql';
The function creates fine, but when I try to execute it I get the
following:
testdb=> select count_records('table1') as output;
NOTICE: plpgsql: ERROR during compile of count_records near line 5
ERROR: unterminated " in name "SELECT
Line 5 is the open cursor line. According to the "PostgreSQL 7.2
Programmer's Guide" (http://www7.us.postgresql.org/users-lounge/docs/7.2/programmer-7.2-US.pdf)
section 23.7.2.2, I believe this line should work. Can anybody see
what I'm doing wrong?
Thanks.
Robert Goodwin
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 2002-03-05 16:46:52 | Mandrake RPMs uploaded |
Previous Message | Bill McGonigle | 2002-03-05 16:41:40 | Re: pg_dumpall storing multiple copies of DB's? |