Skip site navigation (1) Skip section navigation (2)

Re: FW: plpgsql function help

From: "Tyler Hains" <thains(at)profitpointinc(dot)com>
To: "andreas(dot)kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>, pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: FW: plpgsql function help
Date: 2010-03-30 14:00:02
Message-ID: H000006900b5704d.1269957601.mailpa.profitpointinc.com@MHS (view raw or flat)
Thread:
Lists: pgsql-novice
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(dot)kretschmer(at)schollglas(dot)com] 
Sent: Tuesday, March 30, 2010 9:03 AM
To: pgsql-novice(at)postgresql(dot)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

In response to

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2010-03-30 14:09:10
Subject: Re: FW: plpgsql function help
Previous:From: A. KretschmerDate: 2010-03-30 13:03:08
Subject: Re: FW: plpgsql function help

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group