Professional Web Applications Themes

How to return a resultset/table from a sql function? - PostgreSQL / PGSQL

Hi, Is it possible to return the following (parameterized) qyery from a sql or plpsql function, and if so, what is the syntax? SELECT{ (SELECT COUNT(klantnummer) FROM abo_klt WHERE abonnement = $1), (SELECT SUM(aantal_abonnementen) FROM abo_klt WHERE abonnement = $1)); Groeten, Joost ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster...

  1. #1

    Default How to return a resultset/table from a sql function?

    Hi,

    Is it possible to return the following (parameterized) qyery from a sql or plpsql function, and if so, what is the syntax?

    SELECT{ (SELECT COUNT(klantnummer) FROM abo_klt WHERE abonnement = $1),
    (SELECT SUM(aantal_abonnementen) FROM abo_klt WHERE abonnement = $1));




    Groeten,

    Joost

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Joost Kraaijeveld Guest

  2. #2

    Default Re: How to return a resultset/table from a sql function?

    OK, to answer my own question (typo's possible, works here ;-)):


    CREATE TYPE abonnementartikelheader AS (col1 int4, col2 int4);

    CREATE OR REPLACE FUNCTION getabonnementartikelheader(int4)
    RETURNS SETOF abonnementartikelheader AS
    '
    select
    (SELECT COUNT(klantnummer)::int4 FROM abo_klt WHERE abonnement = $1),
    (SELECT SUM(aantal_abonnementen)::int4 FROM abo_klt WHERE abonnement = $1);
    '
    LANGUAGE 'sql' VOLATILE;

    Groeten,

    Joost

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

    Joost Kraaijeveld Guest

Similar Threads

  1. output parameters not working in if return resultset
    By Charts in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: November 4th, 12:59 AM
  2. return query resultset from custom tag
    By vayumahesh in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: June 23rd, 10:17 AM
  3. #24967 [Bgs]: fopen when placed within a function causes function to return
    By laurie at oneuponedown dot co dot uk in forum PHP Development
    Replies: 1
    Last Post: August 7th, 12:12 PM
  4. #24967 [NEW]: fopen when placed within a function causes function to return
    By laurie at oneuponedown dot co dot uk in forum PHP Development
    Replies: 0
    Last Post: August 6th, 09:14 PM
  5. Function/Global var to return name of calling function?
    By Daniel Hansen in forum PHP Development
    Replies: 3
    Last Post: July 6th, 12:32 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