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.
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:
WHERE some_column = 42
AFAIK this is not possible with procedures returning result sets in MS SQL
Shammat, I just checked the linked that you showed me. May I confirm if my syntax is correct?
Microsoft SQL Server
CREATE PROCEDURE uspContacts
SELECT * FROM tblContacts WHERE ContactID = @ContactID
CREATE OR REPLACE FUNCTION ufnContacts() RETURNS SETOF tblContacts AS $BODY$
FOR r IN SELECT * FROM tblContacts
RETURN NEXT r
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.
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.
CREATE OR REPLACE FUNCTION ufnContact(contactID int) RETURNS tblContact
SELECT * FROM tblContacts WHERE contactID = $1;
$$ LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION ufnContacts() RETURNS SETOF tblContact
SELECT * FROM tblContacts;
$$ LANGUAGE 'sql';
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.
CREATE OR REPLACE FUNCTION ufnAccount(varAccountCode bigint) RETURNS SETOF "schLedger"."tbmAccount"
SELECT * FROM "schLedger"."tbmAccount";
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.
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.
I just figured how to use parameters in a function. Here's what I got:
CREATE OR REPLACE FUNCTION ufnAccount(AccountCodeVariable bigint) RETURNS SETOF "schLedger"."tbmAccount"
SELECT * FROM "schLedger"."tbmAccount" WHERE "AccountCode" = $1;
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.