Thread: Dynamic SQL in function

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

robert.goodwin@ums.msfc.nasa.gov (Robert Goodwin) writes:

> 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 " ||

Don't use double quotes.  Use doubled single quotes:

OPEN cursor1 FOR EXECUTE ''SELECT count(*) FROM '' || ...

Double quotes always delimit an identifier.

-Doug
--
Doug McNaught       Wireboard Industries      http://www.wireboard.com/

      Custom software development, systems and network consulting.
      Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...