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

plpgsql function help

From: "Tyler Hains" <thains(at)profitpointinc(dot)com>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: plpgsql function help
Date: 2010-03-29 20:35:09
Message-ID: H000006900b559c2.1269894908.mailpa.profitpointinc.com@MHS (view raw or flat)
Thread:
Lists: pgsql-novice
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

 


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-03-29 23:30:56
Subject: Re: plpgsql function help
Previous:From: Joshua TolleyDate: 2010-03-29 18:59:16
Subject: Re: Clustering/fail-over solution

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