0
votes
1answer
39 views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
2
votes
1answer
581 views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
1
vote
1answer
612 views

PostgreSQL stored function that returns arbitrary resultset

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
1answer
1k views

Error: set_valued function called in context that cannot accept a set. What is it about?

I use Postgresql 9.1, with ubuntu 12.04. Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
1
vote
1answer
432 views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...
0
votes
1answer
36 views

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth. For example: create or replace function ...
0
votes
1answer
276 views

Difference between return next and return record

What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
2
votes
1answer
531 views

returning set of records in stored procedure

I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures: create or ...
2
votes
1answer
2k views

Declare variable of table type in PL/pgSQL

I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like: q1 = select * from foo; q2 = select * from bar; ...
3
votes
1answer
670 views

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
9
votes
2answers
1k views

PostgreSQL procedural languages - differences between PL/pgSQL and SQL

Can anybody please summarize the differences between: http://www.postgresql.org/docs/9.1/static/xfunc-sql.html and http://www.postgresql.org/docs/9.1/static/plpgsql.html ? Main points: ...
16
votes
4answers
4k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
7
votes
5answers
4k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...