Re: plpgsql function help

"Tyler Hains" <thains(at)profitpointinc(dot)com> writes:
> 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