Thread: variable scooping
hi all, I'm a newbie in postgresql. I've tried to make function but having trouble in variable scooping. here is the code in plpgsql: "declare tbl_name varchar:='tbl_A'; begin if exists(select 1 from pg_tables where tablename=tbl_name) then select count(*) from tbl_name; end if; end" the message was: syntax error in $1 in "select count(*) from $1". I thought variable 'tbl_name' was not recognized. Why ? Please need some help here. Thx very much.
On Mon, Jan 30, 2006 at 09:01:37AM +0700, ody quraviharto wrote: > "declare tbl_name varchar:='tbl_A'; > begin > if exists(select 1 from pg_tables where tablename=tbl_name) then > select count(*) from tbl_name; > end if; > end" > > the message was: syntax error in $1 in "select count(*) from $1". You'll need to use EXECUTE for this query; see "Executing Dynamic Commands" in the PL/pgSQL documentation. Here's the link for 8.1 (but use the documentation for whatever version you're running): http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Michael Fuhr