Skip site navigation (1) Skip section navigation (2)

Nested function invocation, but parameter does not exist

From: "Wappler, Robert" <rwappler(at)ophardt(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Nested function invocation, but parameter does not exist
Date: 2010-05-31 16:00:00
Message-ID: C8E2DAF0E663A948840B04023E0DE32A028C7C46@w2k3server02.de.ophardt.com (view raw or flat)
Thread:
Lists: pgsql-general
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)

-- 
	Robert...


Responses

pgsql-general by date

Next:From: Luis Daniel Lucio QuirozDate: 2010-05-31 16:04:04
Subject: Re: What Linux edition we should chose?
Previous:From: Jan StrubeDate: 2010-05-31 15:59:15
Subject: Re: SELECT my_table.varchar FROM my_table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group