Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12

    Unanswered: Function that return a set of data.

    Hello guys!

    It's my first time to visit this forum. I've been learning PostgreSQL, and I find the DBMS quite challenging, as I come from an MSSQL background.

    Question: Can anyone recommend good references about PostgreSQL user-defined functions that return a set of data?

    I think it's not as easy to create "stored procedures" in PostgreSQL as it is in Microsoft SQL Server. But I'm still determined to learn it because PostgreSQL can address my needs at practically zero cost, compared to other proprietary DBMS's.

    Thanks guys, and hoping for a response!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by evander View Post
    Question: Can anyone recommend good references about PostgreSQL user-defined functions that return a set of data?
    The manual?
    e.g. here: http://www.postgresql.org/docs/curre...ENTS-RETURNING


    I think it's not as easy to create "stored procedures" in PostgreSQL as it is in Microsoft SQL Server.
    What do you mean with "easy"?
    The syntax might be a bit different, but then functions returning results are even better in PostgreSQL because they can be used like a table, e.g. you can append a WHERE condition:
    Code:
    SELECT *
    FROM my_function()
    WHERE some_column = 42
    AFAIK this is not possible with procedures returning result sets in MS SQL

  3. #3
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12
    Thanks Shammat!

    I want to know the equivalent syntax of this MSSQL Stored Procedure:

    Code:
    CREATE PROCEDURE uspContacts
         @ContactID bigint
    
    AS
    
         SELECT * FROM tblContacts WHERE ContactID = @ContactID
    I consider this stored procedure "simple" to construct. How does this translate to PostgreSQL?

    Thanks in advance.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by evander View Post
    How does this translate to PostgreSQL?
    See the manual (I posted the link to an example)

    And of course the examples for the CREATE FUNCTION statement:
    http://www.postgresql.org/docs/current/static/sql-createfunction.html

    Code:
    CREATE FUNCTION uspContacts(contact_id int)
    AS $$ 
      SELECT * 
      FROM contacts 
      WHERE contactID = contact_id
    $$
    LANGUAGE SQL;
    Last edited by shammat; 08-25-10 at 09:50.

  5. #5
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12
    Shammat, I just checked the linked that you showed me. May I confirm if my syntax is correct?


    Microsoft SQL Server
    Code:
    CREATE PROCEDURE uspContacts
         @ContactID bigint
    
    AS
    
         SELECT * FROM tblContacts WHERE ContactID = @ContactID

    PostgreSQL 8
    Code:
    CREATE OR REPLACE FUNCTION ufnContacts() RETURNS SETOF tblContacts AS $BODY$
    
         DECLARE
              r tblContacts%rowtype;
    
         BEGIN
              FOR r IN SELECT * FROM tblContacts
              LOOP
                   RETURN NEXT r
              END LOOP;
              RETURN;
         END
         $BODY$
         LANGUAGE 'plpgsql';
    I only copied what I saw, but it would really help if I knew why those keywords should be there, in the create function statement.

    Also, how do I deal with parameters?

    I think this is complicated, but I'm willing to learn. Please bear with me. Thanks.

  6. #6
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12
    Oh your reply came ahead of mine, and it looks even simpler Wait I'll just have a closer look.

  7. #7
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12
    Your own code generates an error saying I need to specify the type or something.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by evander View Post
    I need to specify the type or something.
    That is not a valid PostgreSQL error message.

    Did you read the links to the manual I posted?

    I copied the example from there. I wouldn't be surprised if I made an error while copying.

  9. #9
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    In pg your tables automatically defined as a type. So you can use either RETURNS tblContacts for a single record or RETURNS SETOF tblContacts for multiple records.

    You are not doing anything fancy here so no need for a plpgsql function. Just use a sql function, they are easier and faster.

    Code:
    CREATE OR REPLACE FUNCTION ufnContact(contactID int) RETURNS tblContact
    AS $$
      SELECT * FROM tblContacts WHERE contactID = $1;
    $$ LANGUAGE 'sql';
    
    CREATE OR REPLACE FUNCTION ufnContacts() RETURNS SETOF tblContact 
    AS $$
      SELECT * FROM tblContacts;
    $$ LANGUAGE 'sql';

  10. #10
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12

    Talking

    To Artacus72: Wow, thanks! You saved me from my frustration! I was about to give up on PostgreSQL last night, thinking that it's too complex for me to study.

    I created a function exactly modeled after your own scripts, and this is what I got. It works perfect.

    Code:
    CREATE OR REPLACE FUNCTION ufnAccount(varAccountCode bigint) RETURNS SETOF "schLedger"."tbmAccount"
    AS $$
       SELECT * FROM "schLedger"."tbmAccount";
       $$
       LANGUAGE 'sql';
    
    
    SELECT * FROM ufnAccount(1);
    A few things I observed:
    1. CREATE FUNCTION statement is similar to SQL Server.
    2. Declaring a variable is similar to SQL Server.
    3. I need to specify the keywords RETURNS SETOF so that the function returns a dataset.
    4. I need to specify the schema and the table, and enclose them in double quotes. Why is that?
    5. The AS keyword is similar to SQL Server.
    6. I need to wrap the entire SQL statement that the function will execute inside two pairs of $$. Are those similar to BEGIN and END? If not, why do I have to put them?
    7. The SQL statement inside a function should be terminated with a ;.
    8. I need to specify the language... Can I do dataset looping, cursors, and conditional statements in an SQL function?
    9. The entire function statement is terminated with a ;.

    Next question:
    How do I include the variables (or parameters) in my CREATE FUNCTION statement?

    Thanks a lot, really! I am so happy you gave me the answer to this puzzle.

    Joe

  11. #11
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12
    To Shammat:

    Yes buddy, I have read the manuals. And I do apologize if I hardly catch up on. Disclaimer, I am not a computer science or an information technology graduate. I learned database management by experience. So my learning method is more of a pragmatic one.

    I find the manual sufficient in terms of explaining what it CAN do. But in my humble opinion, it is still difficult for me to understand, especially in this part where I got stuck in Functions. I am surprised that someone pointed me to a solution that is far simpler than what the manual could make me come up with.

    I do not say the manual has shortcomings. After all, it is a manual. But I was expecting a textbook. I really need to find good books about this database because it excites me And on top of that, I need to belong to a community where people, including you, can understand me and my proficiency level.

    Thanks. Do take care.

  12. #12
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12
    I just figured how to use parameters in a function. Here's what I got:

    Code:
    CREATE OR REPLACE FUNCTION ufnAccount(AccountCodeVariable bigint) RETURNS SETOF "schLedger"."tbmAccount"
    AS $$
       SELECT * FROM "schLedger"."tbmAccount" WHERE "AccountCode" = $1;
       $$
       LANGUAGE 'sql';
    
    
    SELECT * FROM ufnAccount(1010);
    Question:
    Why do I place $1 instead of AccountCodeVariable in the WHERE clause... I think I really need to buy a book.

  13. #13
    Join Date
    Aug 2010
    Location
    Philippines
    Posts
    12
    To artacus72: My parameterized function is based on your own example too. You really helped me a lot. Thanks buddy!

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by evander View Post
    4. I need to specify the schema and the table
    If you don't want the schema, simply create your tables in the public schema. The no schema is required. As an alternative you can set up a schema search path, so that you don't need to prefix the table.
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Schemas

    enclose them in double quotes. Why is that?
    Because you created them using double quotes. When using the double quotes, the table (and column) names are considered case-sensitive and thus you need double quotes always (that's what the SQL Standard requires and what SQL Server does completely different)
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Lexical Structure

    I need to wrap the entire SQL statement that the function will execute inside two pairs of $$. Are those similar to BEGIN and END?
    No, they are something similar.

    If not, why do I have to put them?
    The body of a function would normally be needed in single quotes, which makes defining them extremely complicated. That's why the "dollar quoting" was introduced.
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Lexical Structure

    Can I do dataset looping, cursors, and conditional statements in an SQL function?
    No. For that you need PL/pgSQL (but with that creating a set returning function is a bit more complicated).

    How do I include the variables (or parameters) in my CREATE FUNCTION statement?
    I'm not sure I understand that question. You already have a parameter in your function.

    If you mean "how to use it inside the function, the simply reference it using $1, $2 and so on (this is for SQL, in pl/pgSQL you can reference the parameters by name)

    Code:
    CREATE OR REPLACE FUNCTION ufnAccount(varAccountCode bigint) RETURNS SETOF "schLedger"."tbmAccount"
    AS $$
       SELECT * 
       FROM "schLedger"."tbmAccount"
       WHERE accountCode = $1;
     $$
     LANGUAGE 'sql';

  15. #15
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by evander View Post
    I am surprised that someone pointed me to a solution that is far simpler than what the manual could make me come up with.
    Exactly that example is in the manual (the second link I posted)

Posting Permissions

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