Nested function invocation, but parameter does not exist

Поиск
Список
Период
Сортировка
От Wappler, Robert
Тема Nested function invocation, but parameter does not exist
Дата
Msg-id C8E2DAF0E663A948840B04023E0DE32A028C7C46@w2k3server02.de.ophardt.com
обсуждение исходный текст
Ответы Re: Nested function invocation, but parameter does not exist  (Andy Colson <andy@squeakycode.net>)
Список 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...


В списке pgsql-general по дате отправления:

Предыдущее
От: Jan Strube
Дата:
Сообщение: Re: SELECT my_table.varchar FROM my_table
Следующее
От: Luis Daniel Lucio Quiroz
Дата:
Сообщение: Re: What Linux edition we should chose?