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

12
votes
1answer
3k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
24
votes
4answers
10k 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
813 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
647 views

Issue with composite type in an UPSERT function

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. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
32
votes
3answers
23k views

Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert ...
18
votes
5answers
42k views

How to insert (file) data into a PostgreSQL bytea column?

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
3
votes
2answers
1k 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 ...
2
votes
2answers
624 views

How to do WHERE x IN (val1, val2,…) in plpgsql

I would like to update a selection of rows in a table; this works... UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5); But how can I do the same in a plpgsql function? The following gives a ...
2
votes
2answers
5k 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 $$ ... $$ ...
1
vote
1answer
118 views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
1
vote
2answers
117 views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
0
votes
1answer
41 views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to ...
28
votes
2answers
17k 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 ...
10
votes
1answer
2k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
13
votes
5answers
8k 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 ...
5
votes
1answer
877 views

How to access NEW or OLD field given only the field's name?

I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
1
vote
2answers
4k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
8
votes
1answer
89 views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
3
votes
2answers
422 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 ...
4
votes
3answers
550 views

PostgreSQL procedural languages overhead (plpython / plsql / pllua…)

I'm trying to find information about PostgreSQL user defined functions in procedural languages performance for real time tasks. How does they compare to builtin functions? Is there any difference ...
2
votes
1answer
110 views

trigger can't access column loop

SSCCE: The following script: $ cat test.sql CREATE TABLE public.foo ( loop INTEGER ); CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$ SELECT TRUE; $$ LANGUAGE SQL; ...
2
votes
1answer
7k 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
1answer
33 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
1
vote
3answers
48 views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
1
vote
2answers
315 views

Assign to NEW by key in a Postgres trigger

In the trigger body, how can I assign a value to NEW by it's field name? That's what I want to do: some_key = "some_column"; NEW[some_key] = 5;
1
vote
2answers
726 views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
1
vote
2answers
53 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
577 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
378 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
2k 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
62 views

How to avoid duplications using a function

I am using this function in a bulk insert to avoid duplication of url paths. CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4) RETURNS "int4" AS $BODY$ DECLARE ...
0
votes
1answer
113 views

PostgreSQL function thinks table reference exists when it does not

So I run this query: SELECT f_copy_tbl('parts','parts1'); ...while working on code based off of this answer and I get this error message: ERROR: relation "parts1" already exists pgAdmin III ...