pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

tablename as function parameter
2+ messages / 2 participants
[nested] [flat]

* tablename as function parameter
@ 2005-03-15 22:15  Hermann Draeger <hkdraeger@gmx.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Hermann Draeger @ 2005-03-15 22:15 UTC (permalink / raw)
  To: pgsql-sql

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'

fails.

I tested type alternatives to 'text' , such as 'name' or 'sql_identifier' - no success.

What is the right type ?








^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: tablename as function parameter
@ 2005-03-21 05:08  Jonathan Daugherty <cygnus@cprogrammer.org>
  parent: Hermann Draeger <hkdraeger@gmx.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Jonathan Daugherty @ 2005-03-21 05:08 UTC (permalink / raw)
  To: pgsql-sql

# 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-03-21 05:08 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-15 22:15 tablename as function parameter Hermann Draeger <hkdraeger@gmx.de>
2005-03-21 05:08 ` Jonathan Daugherty <cygnus@cprogrammer.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox