Skip site navigation (1) Skip section navigation (2)

Re: Function

From: John DeSoi <desoi(at)pgedit(dot)com>
To: bill house <wchouse(at)bellsouth(dot)net>
Cc: psql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Function
Date: 2010-02-15 03:39:07
Message-ID: 44685007-C8C4-4621-8AD6-0E1ACBD6FDE2@pgedit.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.





In response to

  • Function at 2010-02-14 22:17:51 from bill house

Responses

pgsql-novice by date

Next:From: Lee HorowitzDate: 2010-02-15 04:31:45
Subject: Re: Actions requiring commit
Previous:From: Greg StarkDate: 2010-02-15 02:32:52
Subject: Re: Actions requiring commit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group