Thread: plpgsql function help

"Tyler Hains" <thains@profitpointinc.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

> > 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;