PL/pgSQL is the default procedural language for the PostgreSQL database system. Questions about PL/pgSQL should probably be tagged "PostgreSQL" as well.

learn more… | top users | synonyms

16
votes
4answers
5k 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 ...
10
votes
2answers
368 views

Postgres query plan of a UDF invocation written in pgpsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
6
votes
3answers
487 views

Casting issue when calling a function with composite type parameter

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. what could be the issue? CREATE OR REPLACE FUNCTION ...
2
votes
2answers
1k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
2
votes
2answers
859 views

Running a CTE query in a loop using PL/pgSQL

I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
12
votes
1answer
7k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
6
votes
1answer
1k views

Postgres functions vs prepared queries

I'm sure it's there but I've not been able to find a simple definitive answer to this in the docs or via Google: In Postgres, are prepared queries and user defined functions equivalent as a ...
2
votes
2answers
328 views

Bug in PL/pgSQL function creation

I don't know if this question better suits here or in SO ... This is a script that I'd like to launch (the code of the function was copied from a question on SO): \c mydb create or replace function ...
2
votes
1answer
3k 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; ...
1
vote
2answers
40 views

Can I find errors without using testing data for all the branches of each function?

In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This ...
1
vote
2answers
138 views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: ...
1
vote
1answer
117 views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
1
vote
1answer
735 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 ...