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