Re: plpgsql function help

> > I am trying to get a function to return an integer field pulled from
a
> > different database using dblink. I am getting a run-time error. Here
is
> > the function and the error:

> > CREATE OR REPLACE FUNCTION get_db_id()
> > RETURNS INTEGER AS
> > $BODY$
> > DECLARE
> > client_ids INTEGER[];
> > db_id INTEGER;
> > BEGIN
> > SELECT INTO client_ids DISTINCT client_id FROM clients;
> > SELECT INTO db_id dblink('dbname=system',
> > 'SELECT database_id FROM clients WHERE client_id =
> > '||client_ids[0]);
> > RETURN db_id;
> > END;
> > $BODY$
> > LANGUAGE 'plpgsql' IMMUTABLE
> > COST 100;

> > ERROR: array value must start with "{" or dimension information
> > Context: PL/pgSQL function "get_db_id" line 5 at SQL statement

> Well, you've got a few problems here. You seem to be hoping that
SELECT
> DISTINCT will return an array just because you tell it to assign into
an
> array variable. It will not; INTO does not affect the semantics of
the
> statement, only where the result goes.

> If you are using a version new enough to have array_agg() you could
use
> that to make an array from the client_id values, but I'm rather
unclear
> on the point of this coding anyway. Why are you pulling all of the
> client_id values from the table when you only want to use one? And
> which one do you think you're going to get? (Hint: it'd be pretty
> indeterminate with any coding like this.) There are a number of ways
to
> fix this depending on what you actually need, but it's hard to
recommend
> anything without knowing what the intent is.

> The other problem is that that dblink call won't work, once you get to
> it. dblink needs to have an AS clause telling it what the expected
> result type is. You need something along the lines of

> SELECT INTO db_id * FROM
> dblink('dbname=system', 'SELECT database_id FROM clients WHERE
client_id = '||client_ids[0])
> AS x(database_id int);

> regards, tom lane

Thanks! Here is my working function for the curious:

-- Essentially returns this.database_id while in a client database,
looking
-- it up from the system database.
CREATE OR REPLACE FUNCTION get_db_id()
RETURNS integer AS
$BODY$
DECLARE
rec RECORD;
BEGIN
SELECT client_id INTO rec FROM clients LIMIT 1;
SELECT * INTO rec FROM dblink('dbname=system',
'SELECT database_id FROM clients WHERE client_id =
'||rec.client_id)
AS db(db_id INTEGER);
RETURN rec.db_id;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;