Re: Nested function invocation, but parameter does not exist

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