Обсуждение: return multiple rows

Hi all,
I have a FUNCTION queried like this:
   SELECT * FROM f_authorize_check_query2('%{SQL-User-Name}','%{User-Password}' [...];

Built this way:
   CREATE FUNCTION f_authorize_check_query2(...)  RETURNS radcheck
     AS $_$
    DECLARE
        [...]
        v_ret radcheck%ROWTYPE;
        [...]
           SELECT  INTO v_ret '111',username,'Cleartext-Password',pwd,':=' FROM [...]
    RETURN v_ret;
    END;
   $_$
   LANGUAGE plpgsql;

It returns only one row, like this:
     +----+----------+--------------------+----+-------+
     | id | UserName | Attribute          | op | Value |
     +----+----------+--------------------+----+-------+
     | 1  | bartek   | Cleartext-Password | := | 1234  |
     +----+----------+--------------------+----+-------+

I would like it to return
     +----+----------+--------------------+----+-------+
     | id | UserName | Attribute          | op | Value |
     +----+----------+--------------------+----+-------+
     | 1  | bartek   | Cleartext-Password | := | 1234  |
     | 3  | bartek   | Simultaneous-Use   | := | 1     |
     +----+----------+--------------------+----+-------+

*But*, I would like to _hardcode_ the "Simultaneous-Use" row.
If I use a FUNCTION to build the "Cleartext-Password" row, it's because the data structure
could not have been feetched simply.

Is SELECTing INTO a second time OK?

--
       Architecte Informatique chez Blueline/Gulfsat:
    Administration Systeme, Recherche & Developpement
                                    +261 34 29 155 34

09/11/2009 05:53 PM, Rakotomandimby Mihamina:
> Is SELECTing INTO a second time OK?

But I dont know how...
Any hint?

--
       Architecte Informatique chez Blueline/Gulfsat:
    Administration Systeme, Recherche & Developpement
                                    +261 34 29 155 34

On 11/09/2009 15:53, Rakotomandimby Mihamina wrote:
>
> It returns only one row, like this:
>     +----+----------+--------------------+----+-------+
>     | id | UserName | Attribute          | op | Value |
>     +----+----------+--------------------+----+-------+
>     | 1  | bartek   | Cleartext-Password | := | 1234  |
>     +----+----------+--------------------+----+-------+
>
> I would like it to return
>     +----+----------+--------------------+----+-------+
>     | id | UserName | Attribute          | op | Value |
>     +----+----------+--------------------+----+-------+
>     | 1  | bartek   | Cleartext-Password | := | 1234  |
>     | 3  | bartek   | Simultaneous-Use   | := | 1     |
>     +----+----------+--------------------+----+-------+

You need to declare the function as returning SETOF radcheck, and then
use the RETURN NEXT construct - see the pl/pgsql docs here:

http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

There's an example here of how you do this.

> *But*, I would like to _hardcode_ the "Simultaneous-Use" row.
> If I use a FUNCTION to build the "Cleartext-Password" row, it's because
> the data structure
> could not have been feetched simply.

I'm not sure what you're getting at here.

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------