From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | "Wappler, Robert" <rwappler(at)ophardt(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Nested function invocation, but parameter does not exist |
Date: | 2010-05-31 19:23:22 |
Message-ID: | 4C040CAA.2090604@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Isabella Ghiurea | 2010-05-31 19:45:19 | Re: PG backup performance |
Previous Message | Tom Wilcox | 2010-05-31 19:14:34 | Re: Out of Memory and Configuration Problems (Big Computer) |