Menu
How do I convert a simple select query like select * from customers into a stored procedure / function in pg?
I'm new to Postgres and create function customers() as returns table/setof just didn't feel right and thus the question here.
I understand procs are called "functions" in pg land. Thus create procedurecreate function x() returns setof y returns a paren'd comma separated row of values which can't be used without further processing (at least that's what I'm seeing in pgAdmin and Ruby/Sequel).
create function x() returns table(...) requires I embed the row definition which I don't want to.
I'm sure there's a reason behind all this but I'm surprised that the most common use case is this tricky.
900
PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code.
PostgreSQL 7.3 now supports a much more flexible system for writing set returning functions (SRFs) that when combined with some of the new function permission options allow a greater flexibility in setting up schemas. I assume in this that you already have some experience with writing functions in SQL and PL/pgSQL for PostgreSQL.
First let's look at a simple SQL function that returns an existing table's rowtype. create function GetEmployees () returns setof employee as 'select * from employee;' language 'sql'; This very simple function simply returns all the rows from employee. Let's break down this function.
SRFs can return either a rowtype as defined by an existing table or a generic record type. First let's look at a simple SQL function that returns an existing table's rowtype. create function GetEmployees () returns setof employee as 'select * from employee;' language 'sql'; This very simple function simply returns all the rows from employee.
Untested but should be about right:
CREATE OR REPLACE FUNCTION getcustomers() RETURNS SETOF customers AS $$
SELECT * FROM customers;
$$ LANGUAGE sql;
The issue is "create function x() returns setof y" returns a paren'd comma separated row values which can't be used without further processing
The function returns a row. To decompose into individual columns, call it with:
SELECT * FROM getcustomers();
That's assuming the function defines a proper return type. See:
The manual on CREATE FUNCTION should be a good starting point. The example section covers this topic.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
