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

0
votes
2answers
28 views

Write output from dynamic queries in PL/pgSQL to CSV file

PG_CMD="psql -d portal -U portal -c " PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c " abc() { $PG_CMD " DO \$$ DECLARE srowdata record; ...
1
vote
1answer
79 views

How to put the results of a query into a variable?

I have written a Postgres anonymous block. In which I have written join query on multiple tables like: select a.portal.*, a.p_fb_config.*, a.p_gplus_config from a.portal left outer join ...
2
votes
0answers
28 views

Get inserted row count when master table isn't modified

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables. Now, I need to create ...
-2
votes
1answer
31 views

How to use variable value inside copy function [closed]

I want to send data to .csv file using COPY function in Postgres. But I am using anonymous block so my table name should be value of variable. COPY (SELECT cname.portal from user) To '/tmp/out.csv' ...
0
votes
0answers
23 views

EXECUTE format not working [closed]

DO \$$ DECLARE customer_list varchar[]; customer_schema varchar(100); portal_row a.portal%ROWTYPE; var1 varchar(100); temp varchar(100); ...
0
votes
0answers
16 views

PostgreSQL 9.3.5 database restore error : plpgsql already exists

I have a .net program to restore a PostgreSQL database backup. What it is doing is 1) Delete the existing database. 2) Create it again. 3) Initialize some variables/properties. 4) Restore the ...
1
vote
1answer
68 views

Reuse SELECT query by adding results in array?

I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
8
votes
1answer
2k 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?
1
vote
2answers
47 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
29 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 ...
18
votes
1answer
12k 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 ...
-1
votes
1answer
25 views

Postgresql - Stuck building a plpgsql function [closed]

I am a beginner in plpgsql and i am stuck coding a function. I need a function that does the following: Giving a table, a id_field in that table and another field in that table it does: ORIGINAL ...
1
vote
1answer
1k 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
86 views

UPSERT statement never completes, infinite loop?

Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would ...
1
vote
2answers
60 views

Return the id after insert or select

I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this? Also how can I return the id if the email was ...
2
votes
1answer
102 views

How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4! Here is an SQL fiddle. The ...
3
votes
2answers
57 views

How can I determine if a table exists in the current search_path with PLPGSQL?

I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist. If not, I want to give the user a useful ...
3
votes
1answer
205 views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
1
vote
3answers
159 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 ...
1
vote
2answers
148 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;
2
votes
2answers
440 views

Check whether empty strings are present in character-type columns

I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to ...
2
votes
2answers
90 views

PostgreSQL and storing configs

The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact. Lets take currency as an example. ...
4
votes
2answers
2k 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 ...
4
votes
2answers
204 views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY ...
2
votes
2answers
250 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
20
votes
4answers
7k 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 ...
1
vote
1answer
51 views

Writing a view that caches to another table

I have a table market_trades with columns timestamp, market_id, and amount: api_production=# \d market_trades; Table "public.market_trades" Column | ...
1
vote
1answer
31 views

How to trigger newlines in log messages?

Is it possible for log messages to span multiple lines? I am expecting: RAISE WARNING 'Line 1\nLine 2'; to result in the following log: Line 1 Line 2 But instead I get: Line 1\nLine 2 Any ...
2
votes
1answer
57 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; ...
1
vote
1answer
57 views

why is the name `loop` treated differently in PostgreSQL

I issue the following DDL: CREATE TABLE foo ( loopa INTEGER, loop INTEGER ); Then, examining the DDL in the server (using a client-side tool like DbVisualizer - sorry don't know the query to ...
4
votes
2answers
2k views

Dynamic access to record column in plpgsql function

How can I address a column from a record in a plpgsql function dynamically? In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
1
vote
1answer
32 views

Handling exceptions from inside a function

I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update ...
1
vote
2answers
3k 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 ...
3
votes
2answers
70 views

Removing all columns with given name

I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion. When inserting records ...
6
votes
3answers
591 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 ...
2
votes
1answer
86 views

PL/pgSQL function or rCTE to detect depth of relations between two tables

I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
1
vote
2answers
318 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
3answers
386 views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...
4
votes
2answers
10k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
3
votes
1answer
548 views

What PL/pgSQL debugger can I use?

I'm building a lot of PL/pgSQL functions and a debugger would really come in handy because RAISE NOTICE is not enough. I read about a pgAdmin plugin you can install but it was only on Windows and I'm ...
1
vote
2answers
49 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 ...
0
votes
1answer
49 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
4
votes
2answers
1k views

Call multiple functions from trigger?

I run PostgreSQL-9.2.4 Is it possible to call 2 functions from a trigger? Let's say I have two functions for two different tables to be executed if following trigger fires: Trigger: CREATE TRIGGER ...
1
vote
1answer
206 views

Conditonally create temporary table

Invoking the following SQL leads to a warning 'column "s" has type "unknown"' which I can understand. What I'm trying to accomplish is to conditionally create a temporary in preparation of running a ...
2
votes
1answer
142 views

Is this inconsistent treatment of `void` return from sql and plpgsql functions documented?

begin; create table foo(id integer); create function f1() returns void language sql as $$delete from foo;$$; create function f2() returns void language plpgsql as $$begin delete from foo; end;$$; ...
1
vote
2answers
347 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: ...
3
votes
2answers
213 views

PL/pgSQL fuction to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
0
votes
1answer
425 views

Assign result of regexp_split_to_array() to Pl/pgSQL array variable

How do I create an array and then put a result of a function into it? I'm doing it like this: array text[]; select regexp_split_to_array('whatever this is it splits into array with spaces', E'\\s+') ...
-1
votes
1answer
167 views

Access table field on creating a trigger postgresql

How can I access table field on creating a trigger? CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
2
votes
2answers
3k 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 $$ ... $$ ...