Thread: tablename type?

The PL/PGSQL documentation contains at least two examples of functions
which take an argument of type "tablename", which then serves as a
table name in a query.  Here's one of those examples:

> CREATE FUNCTION concat_selected_fields(tablename) RETURNS text AS '
> DECLARE
>     in_t ALIAS FOR $1;
> BEGIN
>     RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
> END;
> ' LANGUAGE plpgsql;

Typing this at the psql prompt, however, simply results in the
following message:

ERROR:  type tablename does not exist

Is the documentation incorrect, or is there something wrong with my
PostgreSQL 7.4.3 installation?

If the documentation is incorrect, is there another way to achieve
this, or do I have to use a varchar argument and construct and execute
a dynamic command?

DES
--
Dag-Erling Smørgrav - des@des.no

des@des.no (=?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=) writes:
> Typing this at the psql prompt, however, simply results in the
> following message:

> ERROR:  type tablename does not exist

The example is just an example.  Create an actual table and use its
name.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> des@des.no (=?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=) writes:
> > ERROR:  type tablename does not exist
> The example is just an example.  Create an actual table and use its
> name.

If you read the example, you will see that the function is clearly
intended to operate on a table whose name is specified as a parameter
to the function, and that "tablename" is used as the type name for
that parameter, and not a placeholder for an actual table name.

DES
--
Dag-Erling Smørgrav - des@des.no

des@des.no (=?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=) writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> des@des.no (=?iso-8859-1?q?Dag-Erling_Sm=F8rgrav?=) writes:
>>> ERROR:  type tablename does not exist
>> The example is just an example.  Create an actual table and use its
>> name.

> If you read the example, you will see that the function is clearly
> intended to operate on a table whose name is specified as a parameter
> to the function, and that "tablename" is used as the type name for
> that parameter, and not a placeholder for an actual table name.

No, it's you who are misunderstanding.  The example is showing use of a
composite-type parameter (ie, a row value).  Perhaps fleshing out the
example will make it clearer:

regression=# create table tablename(f1 text, f3 text, f5 text, f7 text);
CREATE TABLE
regression=# insert into tablename values('a','b','c','d');
INSERT 577890 1
regression=# CREATE FUNCTION concat_selected_fields(tablename) RETURNS text AS '
regression'# DECLARE
regression'#   in_t ALIAS FOR $1;
regression'# BEGIN
regression'#   RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
regression'# END;
regression'# ' LANGUAGE plpgsql;
CREATE FUNCTION
regression=# select * from tablename t;
 f1 | f3 | f5 | f7
----+----+----+----
 a  | b  | c  | d
(1 row)

regression=# select concat_selected_fields(t.*) from tablename t;
 concat_selected_fields
------------------------
 abcd
(1 row)

            regards, tom lane