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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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