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;