Questions tagged [plpgsql]
PL/pgSQL is the default procedural language for the PostgreSQL database system. Questions about PL/pgSQL should probably be tagged "PostgreSQL" as well.
288
questions
2
votes
1answer
2k views
CentOS 7, PostgreSQL failed to read permission data directory in mounted partition
I've a problem with start PostgreSQL 9.4 on Centos 7 Server. Because the partition /var/lib/pgsql/9.4/data/ (which contains PostgreSQL data) is full so I want to change to another partition.
/dev/...
7
votes
3answers
4k views
How to prevent a PostgreSQL trigger from being fired by another trigger?
I have 2 triggers on one table; one works for INSERTs :
CREATE TRIGGER "get_user_name"
AFTER INSERT ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "add_info"();
This updates some values in the table....
3
votes
1answer
156 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
...
...
0
votes
1answer
1k 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 z)....
3
votes
3answers
3k 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 ...
9
votes
3answers
5k 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
1answer
850 views
Passing array from one plpgsql function to another
I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
4
votes
1answer
6k views
Step through multiple arrays in parallel with FOREACH
Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like:
for(int i = 0 ; i < array1.length ; i++){
...
5
votes
1answer
693 views
Dynamic casting from text within plpgsql
Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query ...
4
votes
1answer
14k 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,
...
2
votes
1answer
98 views
Using an UPDATE trigger to populate Eastings and Northings in PostgreSQL
I am wondering if it is at all possible to create trigger that populates the X and Y of a created point into 2 separate fields called 'easting' and 'northing'?
It is an update trigger on a table ...
2
votes
2answers
34k views
How to cast to int array in Postgresql?
I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code.
Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (...
3
votes
1answer
2k views
How to allow users to run a function, but not access objects the function is accessing
I have a PL/PgSQL stored procedure that performs various operations on tables (SELECT, UPDATE, INSERT, TRUNCATE).
I want users to be able to run the stored procedure, but not themselves perform those ...
a_horse_with_no_name
49.1k10 gold badges97 silver badges128 bronze badges
18
votes
1answer
13k views
EXPLAIN ANALYZE shows no details for queries inside a plpgsql function
I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:
create function f1()
returns integer as
$$
declare
event tablename%ROWTYPE;
....
....
begin
FOR event IN
...
1
vote
1answer
5k views
Trigger function check if all NEW.* fields exist in another table
In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv".
...
4
votes
3answers
4k views
Use trigger to synchronize columns with fields in json column on insert or update
I'm a bit of a database/postgres beginner,so bear with me.
If I have a table, something like this.
CREATE TABLE testy (
id INTEGER REFERENCES other_table,
name varchar(128) PRIMARY KEY,
...
0
votes
1answer
491 views
Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?
I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can ...
1
vote
1answer
4k views
How to execute two transactions concurrently in PostgreSQL?
I want to execute two transactions concurrently in pgAdmin:
--The first transaction should modify the age of Paul
BEGIN;
DECLARE newage INTEGER;
--the original age of Paul is 32
newage := Select age ...
11
votes
2answers
18k views
How to get exception context for a manually raised exception in PL/pgSQL?
In Postgres, we get the "stack trace" of exceptions using this code:
EXCEPTION WHEN others THEN
GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;
This works fine for "natural" ...
3
votes
1answer
272 views
INSERT inside PL/pgSQL function gives different result
In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function ...
0
votes
1answer
2k views
how to delete all rows with empty field via function/trigger combo in Postgres v 9.3?
In PostgreSQL 9.3 I am trying to write a trigger function that will be called after updates to table test2 and that will delete rows of test2 where field "email" is empty. Here's my attempt:
CREATE ...
3
votes
1answer
3k views
anonymous blocks inside a transaction [closed]
I have the following script:
DROP TABLE IF EXISTS a;
CREATE TABLE a(
val VARCHAR NOT NULL
);
DO
$do$
BEGIN
INSERT INTO a(val) VALUES('a1');
INSERT INTO a(val) VALUES('a2');
END;
$do$
...
Erwin Brandstetter
108k11 gold badges229 silver badges358 bronze badges
2
votes
0answers
305 views
Execution plan of query inside pl/pgsql function
Briefly, we have a pl/pgsql function which collects some intermediate data in several temporary tables ("on commit drop") and then returns REFCURSOR which joins those temporary tables with some static ...
0
votes
1answer
73 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
...
1
vote
1answer
805 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 a....
-3
votes
1answer
5k 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' ...
1
vote
1answer
1k 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 ...
0
votes
1answer
2k 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 ...
-2
votes
1answer
77 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 ...
a_horse_with_no_name
49.1k10 gold badges97 silver badges128 bronze badges
1
vote
1answer
676 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 ...
2
votes
2answers
18k 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 ...
3
votes
1answer
5k 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 ...
Erwin Brandstetter
108k11 gold badges229 silver badges358 bronze badges
5
votes
2answers
13k 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
143 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. ...
7
votes
2answers
8k 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 (...
1
vote
1answer
72 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
3k 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 ideas?
2
votes
1answer
664 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;
...
Erwin Brandstetter
108k11 gold badges229 silver badges358 bronze badges
1
vote
1answer
192 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
5k 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
47 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 ...
4
votes
2answers
211 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 ...
7
votes
3answers
854 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 ...
3
votes
1answer
180 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
5k 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 ...
7
votes
2answers
38k 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 ...
4
votes
1answer
3k 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
70 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
86 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:
...
Erwin Brandstetter
108k11 gold badges229 silver badges358 bronze badges
7
votes
2answers
11k 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 ...
