Professional Web Applications Themes

Functions that return RECORD type - PostgreSQL / PGSQL

Hi I come from a MS-SQL background and am trying to figure out what is wrong with the function below: ************************************************** ************************** ************* CREATE OR REPLACE FUNCTION GetAccountInfo (p_AccID int) RETURNS record AS $$ DECLARE r_Return record; BEGIN SELECT a.Field1, a.Field2, a.Field4 INTO r_Return FROM Account WHERE a.AccID = p_AccID; RETURN r_Return; END; $$ language 'plpgsql'; ************************************************** ************************** ************* When I run select * from GetAccountInfo (100) I get the following error message: ERROR: a column definition list is required for functions returning "record" please can someone explain to me how to create a column definition list. Thanks ---------------------------(end of ...

  1. #1

    Default Functions that return RECORD type

    Hi

    I come from a MS-SQL background and am trying to figure out what is wrong
    with the function below:
    ************************************************** **************************
    *************
    CREATE OR REPLACE FUNCTION GetAccountInfo (p_AccID int)
    RETURNS record
    AS
    $$
    DECLARE
    r_Return record;
    BEGIN
    SELECT a.Field1, a.Field2, a.Field4
    INTO r_Return
    FROM Account
    WHERE a.AccID = p_AccID;

    RETURN r_Return;
    END;
    $$
    language 'plpgsql';
    ************************************************** **************************
    *************
    When I run select * from GetAccountInfo (100) I get the following
    error message: ERROR: a column definition list is required for functions
    returning "record"

    please can someone explain to me how to create a column definition list.


    Thanks


    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Craig Bryden Guest

  2. #2

    Default Re: Functions that return RECORD type

    Craig Bryden wrote:
    > When I run select * from GetAccountInfo (100) I get the following
    > error message: ERROR: a column definition list is required for functions
    > returning "record"
    >
    > please can someone explain to me how to create a column definition list.
    CREATE FUNCTION foo() RETURNS SETOF RECORD AS
    'SELECT 1::int,2::int,''A''::text;'
    LANGUAGE sql;

    SELECT * FROM foo() AS (a int, b int, c text);
    a | b | c
    ---+---+---
    1 | 2 | A
    (1 row)

    The other way (which I prefer) is to define a type and change the
    function definition:

    CREATE TYPE foo_res_type AS (a int, b int, c text);
    CREATE FUNCTION foo() RETURNS SETOF foo_res_type ...

    --
    Richard Huxton
    Archonet Ltd

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Richard Huxton Guest

Similar Threads

  1. Bug(?) CFMX return value functions
    By JorritJ in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: May 19th, 08:38 AM
  2. PL/pgSQL functions and RETURN NEXT
    By John Sidney-Woollett in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 30th, 03:54 PM
  3. Functions returning RECORD
    By Craig Bryden in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 14th, 01:37 PM
  4. [PHP] functions/methods and what they should return
    By Dan Anderson in forum PHP Development
    Replies: 5
    Last Post: August 20th, 10:40 PM
  5. functions/methods and what they should return
    By Chris W. Parker in forum PHP Development
    Replies: 0
    Last Post: August 20th, 09:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139