Nested function invocation, but parameter does not exist

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...

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