Date: Sun, 20 Mar 2005 21:08:20 -0800
From: Jonathan Daugherty <cygnus@cprogrammer.org>
To: pgsql-sql@postgresql.org
Subject: Re: tablename as function parameter
Message-ID: <20050321050820.GD20336@vulcan.cprogrammer.org>
References: <d17o8m$dfd$01$1@news.t-online.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <d17o8m$dfd$01$1@news.t-online.com>
User-Agent: Mutt/1.5.6+20040907i

# I'm trying to create a function (language 'sql') with table name as
# single input parameter, but I always get syntax errors at
# "$1". Probably I am use wrong types.
# 
# simple example:
# 
# create function testfct (text) language sql as 'select count(*) from
# $1'

This doesn't work because the value of $1 isn't literally substituted
into the SQL function that you've created; it's treated as a value
token that can only be used in certain contexts.

If you want to select records from an arbitrary table, you can use the
table name parameter to build and execute a cursor in plpgsql.  In my
experience, return values can't be quite so polymorphic without a lot
of pain and suffering but, then again, using SELECT * FROM $table
inside a function and expecting to return all of the results is
probably not something you'll need very often.

But here's a way, to be didactic:

CREATE OR REPLACE FUNCTION test(text) RETURNS SETOF record AS '
DECLARE
  _table ALIAS FOR $1;
  _mycursor refcursor;
  _row record;
BEGIN
  OPEN _mycursor FOR EXECUTE ''SELECT * FROM '' || _table;

  FETCH _mycursor INTO _row;
  WHILE FOUND LOOP
    RETURN NEXT _row;
    FETCH _mycursor INTO _row;
  END LOOP;

  RETURN;
END
' LANGUAGE plpgsql;

If you return SETOF RECORD, you'll need to be explicit about how the
return value is treated, depending on what you expect to get back from
the function:

mydb> SELECT * FROM test('mytable') AS (col1 integer, col2 text, col3
date);

-- 
  Jonathan Daugherty
  Command Prompt, Inc. - http://www.commandprompt.com/
  PostgreSQL Replication & Support Services, (503) 667-4564