Dynamic SQL in function
От | robert.goodwin@ums.msfc.nasa.gov (Robert Goodwin) |
---|---|
Тема | Dynamic SQL in function |
Дата | |
Msg-id | 222613cc.0203050846.3e491656@posting.google.com обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: