Thread: FW: plpgsql function help

FW: plpgsql function help

From
"Tyler Hains"
Date:

Hello,

 

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

 

ERROR: array value must start with "{" or dimension information

SQL state: 22P02

Context: PL/pgSQL function "get_db_id" line 5 at SQL statement

 

 

Can anyone tell me what I’m doing wrong?

 

Thanks,

Tyler Hains

ProfitPoint, Inc.

www.profitpointinc.com

 

Re: FW: plpgsql function help

From
"A. Kretschmer"
Date:
In response to Tyler Hains :
> Hello,
>
>
>
> 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;
>
>
>
> ERROR: array value must start with "{" or dimension information
>
> SQL state: 22P02
>
> Context: PL/pgSQL function "get_db_id" line 5 at SQL statement
>
>
>
>
>
> Can anyone tell me what I?m doing wrong?

You have defined client_ids as INT[], but your select returns a SET of
integers. You can change it to :

SELECT INTO client_ids array_agg(DISTINCT client_id) FROM clients;



Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Re: FW: plpgsql function help

From
"Tyler Hains"
Date:
Alas, I'm running version 8.3. Is there an easy way to do it in 8.3? Can
I create that function in 8.3, or is it dependent upon other 8.4
features? Would it cause any migration issues later? Where can I find
the function without downloading all of the 8.4 source?

Thanks!
Tyler Hains
ProfitPoint, Inc.
www.profitpointinc.com


-----Original Message-----
From: A. Kretschmer [mailto:andreas.kretschmer@schollglas.com]
Sent: Tuesday, March 30, 2010 9:03 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] FW: plpgsql function help

In response to Tyler Hains :
> Hello,
>
>
>
> 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;
>
>
>
> ERROR: array value must start with "{" or dimension information
>
> SQL state: 22P02
>
> Context: PL/pgSQL function "get_db_id" line 5 at SQL statement
>
>
>
>
>
> Can anyone tell me what I?m doing wrong?

You have defined client_ids as INT[], but your select returns a SET of
integers. You can change it to :

SELECT INTO client_ids array_agg(DISTINCT client_id) FROM clients;



Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice



Re: FW: plpgsql function help

From
"A. Kretschmer"
Date:
In response to Tyler Hains :
> Alas, I'm running version 8.3. Is there an easy way to do it in 8.3? Can
> I create that function in 8.3, or is it dependent upon other 8.4
> features? Would it cause any migration issues later? Where can I find
> the function without downloading all of the 8.4 source?

Okay, no problem ;-)

You can write a own aggregate, see:
http://www.postgresql.org/docs/current/static/xaggr.html

search for CREATE AGGREGATE array_accum


Please, no top-posting and fullquote below, okay?


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99