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

From: "Mourad EL HADJ MIMOUNE" <mimoune(at)ensma(dot)fr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: the use of expressions instead of simple values in array insertion?
Date: 2001-06-07 09:41:56
Message-ID: 003a01c0ef36$178e2d40$71a337c1@ensma.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Nicolas Huillard 2001-06-07 09:48:24 RE: Accessing two differents database in a SELECT
Previous Message Jose Manuel Lorenzo Lopez 2001-06-07 09:37:34 postgresql on SuSE 7.1 with reiserfs