dynamic table names, determined by calling parameter

From: "Michael Beckstette" <mbeckste(at)TechFak(dot)Uni-Bielefeld(dot)DE>
To: pgsql-sql(at)postgresql(dot)org
Subject: dynamic table names, determined by calling parameter
Date: 2002-05-27 16:07:43
Message-ID: 1020527180743.ZM18265@teak.TechFak.Uni-Bielefeld.DE
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

sorry, when this is a silly question, but i wonder why the following pl/pgsql
functions works:

CREATE FUNCTION test (text) RETURNS TEXT AS '
DECLARE
table_name ALIAS FOR $1;

BEGIN
-- test_table is an existing table
CREATE TEMPORARY TABLE temp1 AS SELECT * FROM test_table WHERE (hsp_rank=1 AND
hsp_evalue<=evalue_cutoff);
RETURN table_name;
END;
' LANGUAGE 'plpgsql';

and this one with the table name specified by the calling parameter not:

CREATE FUNCTION test (text) RETURNS TEXT AS '
DECLARE
table_name ALIAS FOR $1;

BEGIN

CREATE TEMPORARY TABLE temp1 AS SELECT * FROM table_name WHERE (hsp_rank=1 AND
hsp_evalue<=evalue_cutoff);
RETURN table_name;
END;
' LANGUAGE 'plpgsql';

Result:
prod2_db=#SELECT test('test_table');
ERROR: parser: parse error at or near "$1"

Any idea ?

Regards
Michael Beckstette

Browse pgsql-sql by date

  From Date Subject
Next Message Christopher Kings-Lynne 2002-05-27 18:24:31 Re: Trees in SQL
Previous Message Marc SCHAEFER 2002-05-27 14:12:28 Some additional PostgreSQL questions