Re: Function

On Feb 14, 2010, at 5:17 PM, bill house wrote:

> CREATE OR REPLACE FUNCTION disp_dup_recs(varchar, varchar)
>  RETURNS SETOF test_table
>  AS $$ SELECT *
>        FROM $1
>        WHERE $2
>        IN (SELECT $2
>            GROUP BY $2
>            HAVING ( COUNT($2) > 1 )
>           );
>      $$
>    LANGUAGE SQL
>    STABLE;
> 
> --returns
> --ERROR:  syntax error at or near "$1"
> --LINE 81:         FROM $1


You can't build SQL statements like this. If you want to build a statement dynamically (where the table name and column references are not known when the function is defined) you need to use EXECUTE. See

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


John DeSoi, Ph.D.