Search Postgresql Archives

Re: Nested function invocation, but parameter does not exist

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 05/31/2010 11:00 AM, Wappler, Robert wrote:
Hi list,
I want to create an install script for a database. First a schema and
its elements are created in a second approach, some adjustments are
done, e.g. create rows, which can be referenced as defaults instead of
having NULL in the referenced column. Below is a minimum non-working
example.

The procedure create_default_ref_target() creates the actual row, which
should be referenced and has to return the automatically generated key.
The table reference should reference the row just generated, if there is
nothing else known. So the procedure alter_default_ref(int) alters the
table. But if alter_default_ref(int) is invoked, there is now parameter
$1. I do not really understand this. Invoking
create_default_ref_target() alone creates the row and returns a value.

Thanks for your help.

---- Example:
CREATE TABLE referenced (id serial PRIMARY KEY, str text);
CREATE TABLE referencee (id serial PRIMARY KEY, ref int REFERENCES
referenced (id) NOT NULL);
CREATE OR REPLACE FUNCTION create_default_ref_target() RETURNS int
VOLATILE AS $$
        INSERT INTO referenced (str) VALUES ('default ref target')
RETURNING id;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION alter_default_ref(_ref int) RETURNS void
VOLATILE AS $$
        ALTER TABLE referencee ALTER COLUMN ref SET DEFAULT $1;
$$ LANGUAGE SQL;

---- Invocations:
SELECT alter_default_ref(create_default_ref_target());
ERROR:  there is no parameter $1
KONTEXT:  SQL function "alter_default_ref" statement 1
db=>  SELECT alter_default_ref(create_default_ref_target());
ERROR:  there is no parameter $1
KONTEXT:  SQL function "alter_default_ref" statement 1
db=>  SELECT * FROM referenced;
  id | str
----+-----
(0 Zeilen)


You don't understand what you wrote?  Or you didn't write it?  You dont understand the $1?  Its kinda a strange setup, but, I'm gonna guess what you need is:

CREATE OR REPLACE FUNCTION alter_default_ref(_ref int) RETURNS void
VOLATILE AS $$
        ALTER TABLE referencee ALTER COLUMN ref SET DEFAULT _ref;
$$ LANGUAGE SQL;


or

CREATE OR REPLACE FUNCTION alter_default_ref(_ref int) RETURNS void
VOLATILE AS $$
	execute 'ALTER TABLE referencee ALTER COLUMN ref SET DEFAULT ' || _ref;
$$ LANGUAGE SQL;


-Andy

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux