Обсуждение: the use of expressions instead of simple values in array insertion?

Поиск
Список
Период
Сортировка

the use of expressions instead of simple values in array insertion?

От
"Mourad EL HADJ MIMOUNE"
Дата:
Hi,
I encounter a problem whene I wanted use expressions to inset values in
column whose values are of array type.
the following example illustrates this problem:
CREATE TABLE address (Num INT4, street VARCHAR, city VARCHAR, contry
VARCHAR);
CREATE TABLE person (name VARCHAR, add OID[]);

CREATE FUNCTION address_oid (VARCHAR) returns OID AS'
SELECT  OID from address s  where s.city = $' LANGUAGE 'SQL';

INSERT INTO address VALUES (5, 'bd Les Champs Elizy', 'Paris' , 'France');
INSERT INTO address VALUES (10, 'Place  Leclerc', 'Poitiers' , 'France');

the question is : how we can insert values in person table by using
address_oid function.
the solution I find is to make this in several steps :
INSERT INTO person VALUES ('Dupont', '{}');

Update person set add[1] = address_oid ('Poitiers');
Update person set add[1] = address_oid ('Paris');

can someone know how we can make this in one step without using update
commad.

thanks for your help,

Mourad.