These functions are used in the same fashion as the first function, This limitation may be removed in a future version. Currently, SRF returning PL/pgSQL functions must generate the entire result set before returning although if the set becomes large it will be written to disk. This does not cause the function to return. The body of the loop is the new return form, 'return next' which means that an output row is queued into the return set of the function. The main body does a loop over the group by query stated setting r to each row in sequence. This variable will be used to store the rows coming from the query in the main body of the function. The function starts off by declaring a variable r to be of the rowtype holder. The PL/pgSQL function is a little more complicated, but let's go through it. The rows that it returns are defined by the group by query in its body. It returns a rowset of rows defined by the type holder (int, int8). The SQL is very similar to the GetEmployee() function above. Select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentidĬreate or replace function PLpgSQLDepartmentSalaries() returns setof holder asįor r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop For this function we'll write a version in SQL and then a version in PL/pgSQL:Ĭreate function SqlDepartmentSalaries() returns setof holder as
We can then define functions that return sets of this type. Here we are defining a new type named holder which is a composite type of an integer named departmentid and a bigint named totalsalary. If you want to return an existing record type, you need to make a dummy type to hold the output type, for example:Ĭreate type holder as (departmentid int, totalsalary int8) This is great, but what if you wanted to return something more complicated, for example, a list of departments and the total salary of all employees in that department. Select * from GetEmployees() where id > 2 For example, to use this function to get all the information on employees with an id greater than 2 you could write: The body of the function is a very simple SQL statement to generate the output rows.Īn SRF can be used in place of a table or subselect in the FROM clause of a query. The return type of the function is setof employee, meaning it is going to return a rowset of employee rows. This very simple function simply returns all the rows from employee. First let's look at a simple SQL function that returns an existing table's rowtype.Ĭreate function GetEmployees() returns setof employee as 'select * from employee ' language 'sql' SRFs can return either a rowtype as defined by an existing table or a generic record type. Insert into employee values (3, 'Jack Jackson', 60000, 2) Insert into employee values (2, 'Jane Doe', 50000, 1) Insert into employee values (1, 'John Smith', 30000, 1) Insert into department values (1, 'Management')
We're going to work with a very simple set of tables and data and functions written in SQL and PL/pgSQL.Ĭreate table department(id int primary key, name text) Ĭreate table employee(id int primary key, name text, salary int, departmentid int references department) I assume in this that you already have some experience with writing functions in SQL and PL/pgSQL for PostgreSQL. What browser and OS are you using? firefox, chrome - linux, windowsĭid clearing your cache and cookies resolve the issue? You can quickly clear your cache by refreshing your browser while holding the Shift key.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. So the question is how can google spreadsheets and google app engine work together? A friend suggests reading the entire spreadsheet (or larger portions) and save it in memcache. I guess my question is why does the google spreadsheet api take more than 10 seconds to return one record (maybe 200 bytes) Isn't the request inside the google data centers and shouldn't it be very fast? Or can urlfetch make a special case for google apis and do it internally and not count it as a timeout. Is there a way to optimize the google data api read call? But I'm wondering if there are other options. One option I have is to run the load task on the development server and upload the data. I have a spreadsheet of 4000 lines, but I only read one line at a time and it returns in sometimes much more than 10 seconds. Is there any way to optimize this to be able to read a single line from the docs spreadsheet feed. I also am using google docs data api from appengine.
0 kommentar(er)
