FAQ Database Discussion Community


Simpify PL/pgSQL function with lots of if-else statements and JSONification

sql,json,postgresql,stored-procedures,plpgsql
I have PL/PgSQL function that performs quite similar queries (the only thing that they have different is column names) and converts the output into JSON object. CREATE OR REPLACE FUNCTION get_observations(kind varchar, site_id integer, var varchar) RETURNS TABLE (fc json) AS $func$ BEGIN IF kind = 'raw' THEN IF var...

Transaction results in syntax error

postgresql,plpgsql
This function is created ok without the transaction block. With it as in the following I get the following error... ERROR: syntax error at end of input LINE 27: $$ ^ ********** Error ********** ERROR: syntax error at end of input If I take out the 'COMMIT' and corresponding 'BEGIN'...

CRC32 function with PL/pgSQL

postgresql,plpgsql,crc,crc32
How to compute a 32 bits cyclic redundancy check (CRC-32) as a function in PostgreSQL, the same way as MySQL?

Elegant way of handling PostgreSQL exceptions?

sql,postgresql,exception-handling,plpgsql,dynamic-sql
In PostgreSQL, I would like to create a safe-wrapping mechanism which returns empty result if an exception occurs. Consider the following: SELECT * FROM myschema.mytable; I could do the safe-wrapping in the client application: try { result = execute_query('SELECT value FROM myschema.mytable').fetchall(); } catch(pg_exception) { result = [] } But...

How to use variable as table name?

sql,postgresql,stored-procedures,plpgsql,dynamic-sql
BEGIN _table_name := 'mytable'; CREATE TEMPORARY TABLE _table_name ( id integer NOT NULL, name character varying, active boolean ) ON COMMIT DROP'; -- logic here RETURN QUERY SELECT table1.id, table1.name FROM _table_name AS table1; END; I have simplified my problem. I'm trying to use a variable as the table name....

postgresql 9.1 invalid input syntax for type numeric

postgresql,plpgsql
I am build a function that is a little complicated. Complicated my my standards. When I execute the code below, using this command: select * from populate_lt_downside_volatility( '02-Sept-2014' , '05-Sept-2014' , 5, 'df_1') I get these messages: NOTICE: curr_sec_key: S5COND_INDEX NOTICE: curr_anchor_date.price_date: 2014-09-02 ERROR: invalid input syntax for type numeric:...

Referring to session variables (\set var='value') from PL/PGSQL

postgresql,variable-scope,plpgsql,psql
I can pass variables into PostgreSQL using psql --variable="var='value'" <<<'SELECT :var' ...and refer to them as, in this case, :var in SQL queries passed to psql on stdin. However, this doesn't work from code using PL/PGSQL: psql --variable=var="'value'" <<'EOF' DO $$ BEGIN SELECT :var; END; $$ EOF ...yielding the error:...

Safe way to open cursor with dynamic column name from user input

sql,sql-injection,plpgsql,dynamic-sql,postgresql-9.4
I am trying write function which open cursor with dynamic column name in it. And I am concerned about obvious SQL injection possibility here. I was happy to see in the fine manual that this can be easily done, but when I try it in my example, it goes wrong...

OVERLAPS operator to check multiples rows

postgresql,database-design,plpgsql,overlap
Currently I am using the overlaps operator so that users can't insert dates that overlap. I created this function: CREATE OR REPLACE FUNCTION overlap(docent_medewerkerscode1 CHARACTER(7), lokaal1 TEXT, groep_groepsnaam1 TEXT, startdatum1 timestamp, einddatum1 timestamp) RETURNS TEXT AS $$ DECLARE resultaat TEXT; rec rooster%rowtype; BEGIN FOR rec IN SELECT groep_groepsnaam = groep_groepsnaam1...

Postgres %% in function

sql,postgresql,plpgsql
What does the "%%" in this statement mean? SELECT nextval(seq_name) %% 1024 INTO seq_id; And why does Postgres say, when I use it? operator does not exist: bigint %% integer ...

PostgreSQL modifying fields dynamically in NEW record in a trigger function

postgresql,plpgsql
I have a user table with IDs and usernames (and other details) and several other tables referring to this table with various column names (CONSTRAINT some_name FOREIGN KEY (columnname) REFERENCES "user" (userid)). What I need to do is add the usernames to the referring tables (in preparation for dropping the...

PL/pgSQL Looping through multiple schema, tables and rows

postgresql,loops,plpgsql,dynamic-sql
I have a database with multiple identical schemas. There is a number of tables all named 'tran_...' in each schema. I want to loop through all 'tran_' tables in all schemas and pull out records that fall within a specific date range. This is the code I have so far:...

Error on Dynamic csv file export using plpgsql copy to csv in a function

postgresql,plpgsql,export-to-csv,postgresql-copy
I am trying to filter a postgresql table for rows that have a product id as a foreign key. For each product id, I need to export 1 csv each to a folder eg , prod1.csv, prod2.csv etc. I have tried to create the function below to automate this but...

Drop function in Postgres

sql,postgresql,plpgsql,identifier,sql-function
I have the following function: CREATE FUNCTION "updateStat"(_request_date timestamp without time zone, _calls integer, _latency integer) RETURNS void AS $$ BEGIN LOCK TABLE "statistics" IN SHARE ROW EXCLUSIVE MODE; WITH upsert AS (UPDATE "statistics" set calls = calls + _calls, total_latency = total_latency + _latency WHERE request_date=_request_date RETURNING request_date) INSERT...

Using Exception handling to fix foreign key constraint in PL/pgsql (PostgreSQL)

postgresql,plpgsql
Trying to learn exception handling in pgSQL (PostgreSQL 9.1). The following SP fails with ERROR: insert or update on table "dx" violates foreign key constraint "fk_icd9" SQL state: 23503 Detail: Key (cicd9, cdesc)=(244.9, testing1) is not present in table "icd9". fk_icd9 is defined from table dx as: CONSTRAINT fk_icd9 FOREIGN...

How to pass a parameter into a date function

sql,postgresql,parameter-passing,plpgsql,date-arithmetic
I am trying to create a simple function and I cannot seem to pass in a parameter into the date function. Here is the function: CREATE OR REPLACE FUNCTION test(source int,days text) RETURNS integer AS $totals$ declare totals integer; BEGIN select count(id) into totals from ad where createdate::date = date(current_date...

Endless loop in trigger function

postgresql,triggers,plpgsql,postgresql-9.1
This is a trigger that is called by either an insert, update or a delete on a table. It is guaranteed the calling table has all the columns impacted and a deletes table also exists. CREATE OR REPLACE FUNCTION sample_trigger_func() RETURNS TRIGGER AS $$ DECLARE operation_code char; table_name varchar(50); delete_table_name...

Cannot create plpgsql function using psql -f filename option

postgresql,plpgsql
I am using Postgres 8.4 and I have tried to run a create function ... script from the command line using psql dbname -U username -f filename or psql -f filename -d dbname -U username and it always results in the following error psql:mergenodedata.sql:40: ERROR: syntax error at or near...

Error while concatenating plpgsql var with query on cursor statement

sql,database,postgresql,plpgsql,postgresql-9.3
I am getting error trying concatenate the var sch in the second For: ERROR: syntax error in or next a "||" SQL state: 42601 Character: 1151 Does anyone know how to solve this problem concatenation? CREATE OR REPLACE FUNCTION generate_mallet_input2() RETURNS VOID AS $$ DECLARE sch name; r record; BEGIN...

Error while trying to insert data using plpgsql

postgresql,timestamp,plpgsql
I have the following plpgsql function: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) RETURNS bigint AS $BODY$ DECLARE current_time timestamp with time zone = now(); BEGIN INSERT INTO "TEST"( created) VALUES (current_time) RETURNING id INTO pid; END $BODY$ LANGUAGE plpgsql; select * from test_func(); The above gives an error: column...

Invalid input syntax PLPGSQL script

postgresql,plpgsql
I have a trigger function that is going to audit transactions made on a table. CREATE or REPLACE FUNCTION audit() returns trigger language plpgsql as $$ begin EXECUTE FORMAT ('INSERT INTO %I.audit VALUES (%L,%L,%L)',TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, now()); RETURN NEW; end; $$ However I keep getting "ERROR: invalid input syntax for...

Informix to PostgreSQL - Transaction Function

sql,postgresql,transactions,plpgsql,informix
I need to convert my Informix function to PostgreSQL. Problem is that I know PostgreSQL doesn't allow to call BEGIN WORK and COMMIT in function so I don't know how to handle my exceptions and rollback that way. Function that I want to convert looks like this: CREATE PROCEDURE buyTicket(pFlightId...

plpgsql - How to return out parameter and ref cursor from stored procedure?

postgresql,stored-procedures,plpgsql,out-parameters,ref-cursor
I would like to implement paging in my application. I created stored procedure that returns number of records as output parameter and ref cursor- data itself (with limits and offsets) But as result-I'm getting -" function result type must be bigint because of OUT parameters" As far as I understand-...

Postgres return null values on function error/failure when casting

sql,postgresql,casting,plpgsql
I am attempting to convert text values to timestamp values. For the following table called a: id | c1 ----+-------------------- 1 | 03-03-2000 2 | 01-01-2000 3 | 12/4/1990 4 | 12 Sept 2011 5 | 12-1-1999 12:33:12 6 | 24-04-89 2:33 am I am attempting to perform a select...

How to use triggers to prevent duplicate records in PostgreSQL?

postgresql,database-design,triggers,constraints,plpgsql
I wish to create a stored procedure (in plpgsql, PostgreSQL 9.1) that first checks to be sure that the record which is going to be inserted is unique on four of its columns, or if a record is updated, that it is updated to unique values. Example: Record (1,2,3,4) is...

PL/pgSQL trigger to stop a river crossing another river

sql,postgresql,postgis,plpgsql,spatial
I have to write a trigger to stop a river crossing another river. I'm really struggling with it and any help would be appreciated. myriver is the table containing all the information on the rivers. So upon insert of a new river if it crosses an existing river, I should...

Create trigger on create role

postgresql,triggers,plpgsql
I have a PostgreSQL database. I create new READ-ONLY users as follows: $ sudo -upostgres psql postgres postgres=# CREATE ROLE readonly; postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; postgres=# BEGIN; postgres=# CREATE ROLE "<PUT_READONLY_USERNAME_HERE>" WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE' IN ROLE readonly; postgres=# COMMIT; Also I...

PostgreSQL: How to DROP FUNCTION IF EXISTS without specifying parameters?

mysql,postgresql,plpgsql
I can successfully create a function as follows: CREATE FUNCTION Foo(MY_Value INT) RETURNS INT AS 'SELECT 2 + MY_Value' LANGUAGE SQL However, if I first want to check if the function exists and then drop it if I does, I must specify the following: DROP FUNCTION IF EXISTS Foo(My_Value INT);...

Execute String Postgres

database,postgresql,triggers,plpgsql,postgresql-9.1
I am having trouble getting this code to work. This is executed as a result of a insert, update or delete trigger. The same code is going to be called from several table triggers. Therefore, I don't know pre-hand what the table names are or what their columns are. Any...

Is there a way to disable function overloading in Postgres

postgresql,types,casting,plpgsql,postgresql-9.3
My users and I do not use function overloading in PL/pgSQL. We always have one function per (schema, name) tuple. As such, we'd like to drop a function by name only, change its signature without having to drop it first, etc. Consider for example, the following function: CREATE OR REPLACE...

Postgres syntax error in a function

postgresql,plpgsql,dynamic-sql,reserved-words
I am trying to create a function and I can't find my error in the following code: CREATE OR REPLACE FUNCTION qwat_od.fn_label_create_fields(table_name varchar, position boolean = true, rotation boolean = true) RETURNS void AS $BODY$ BEGIN /* Creates columns */ EXECUTE 'ALTER TABLE qwat_od.'||table_name||' ADD COLUMN label_1_visible smallint default 1;...

Postgresql: execute update on a temporary table in plpgsql is not working

postgresql,sql-update,plpgsql,execute,temp-tables
I'm trying to update a field in a temporary table I've created. The code for the temporary table looks like this: CREATE OR REPLACE FUNCTION insertTable () RETURNS VOID AS $$ BEGIN execute 'create temporary table myTable (id INTEGER, value TEXT) on commit preserve rows'; execute 'insert into myTable values(1,...

Select every first element of array of integer arrays to array

arrays,postgresql,plpgsql
How to select every first element of array of integer arrays to array? {{1,2,3},{2,15,32},{5,16,14},...} -> {1,2,5,...}

name of value returned from PostgreSQL function

postgresql,plpgsql
I'm working on some code that talks to a PostgreSQL server for the first time (my experience is with SQL Server) and I need help figuring something out. I've always tried to avoid "SELECT * ..." from code, but I can't figure out what the alternative is when calling a...

Alternatives to WITH .. AS .. clause in PostgreSQL

sql,postgresql,plpgsql,common-table-expression,postgresql-9.3
I have several big queries of the following type (simplified for clarity). create function myfunction() returns void as $$ begin ... with t as ( total as total, total * 100 / total as total_percent, total / people.count as total_per_person, part1 as part1, part1 * 100 / total as part1_percent,...

PostgreSQL - How to dynamically execute a query in a function and return table

sql,postgresql,plpgsql
I am trying to write a function, to which if I pass a variable then depending upon the value of the variable, different query should execute. CREATE OR REPLACE FUNCTION SW_Versions(VersionType varchar) RETURNS TABLE(array_sw_version varchar) AS $$ BEGIN IF VersionType = 'All' THEN EXECUTE 'select ''1'' as array1_sw_version UNION ALL...

How to remove duplicate rows with foreign keys dependencies?

database,postgresql,exception-handling,foreign-keys,plpgsql
I'm sure this is common place, but Google is not helping. I am trying to write a simple stored procedure in PostgreSQL 9.1 that will remove duplicate entries from a parent cpt table. The parent table cpt is referenced by a child table lab defined as: CREATE TABLE lab (...

PostgreSQL 9.3: isnumeric() in a condition

postgresql,plpgsql,postgresql-9.3,isnumeric
I need to check whether the given text is numeric or not from the function. Creating function for isnumeric(): CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$ DECLARE x NUMERIC; BEGIN x = $1::NUMERIC; RETURN TRUE; EXCEPTION WHEN others THEN RETURN FALSE; END; $$ LANGUAGE plpgsql IMMUTABLE; Function from...

PostgreSQL function execute query

sql,postgresql,plpgsql,postgresql-9.3
I want to run a SQL query if a condition is met, but I get the following error: ERROR: a separate $ chain is unfinished in or near «$func$ my SQL query is: CREATE OR REPLACE FUNCTION myfunc() RETURNS TABLE(dateticket date, timeticket time, userid integer, my_all bigint) AS $func$ BEGIN...

How to use mixed int and numeric arguments in a Postgres 9.1+ function

sql,postgresql,parameter-passing,plpgsql,iif
I'm looking for a way to create an icase() function which works with any second and third parameter compatible data types. I tried in Postgres 9.4: CREATE OR REPLACE FUNCTION public.icase( cond1 boolean, res1 anyelement, conddefault anyelement) RETURNS anyelement AS ' SELECT CASE WHEN $1 THEN $2 ELSE $3 END;...

Stored procedure syntax with IN condition

arrays,postgresql,stored-procedures,parameter-passing,plpgsql
(1) =>CREATE TABLE T1(id BIGSERIAL PRIMARY KEY, name TEXT); CREATE TABLE (2) =>INSERT INTO T1 (name) VALUES ('Robert'), ('Simone'); INSERT 0 2 (3) SELECT * FROM T1; id | name ----+-------- 1 | Robert 2 | Simone (2 rows) (4) CREATE OR REPLACE FUNCTION test_me(id_list BIGINT[]) RETURNS BOOLEAN AS $$...

Is there any way to call a c/c++ file from a trigger in postgresql?

database,postgresql,plsql,triggers,plpgsql
I want to call a c++/bash-script file through a trigger in postgresql. Is there any way for doing this?

PostgreSQL obtain and release LOCK inside stored function

sql,postgresql,plpgsql
I have a function that needs to perform a long update on multiple large tables. During the update 2-3 tables at a time need to be locked in EXCLUSIVE mode. Since not all the tables need to be locked at the same time, ideally I'd want to LOCK only those...

Execute multiple functions together without losing performance

postgresql,plpgsql,database-performance,query-performance,postgresql-performance
I have this process that has to make a series of queries, using pl/pgsql: --process: SELECT function1(); SELECT function2(); SELECT function3(); SELECT function4(); To be able to execute everything in one call, I created a process function as such: CREATE OR REPLACE FUNCTION process() RETURNS text AS $BODY$ BEGIN PERFORM...

URL-encoding VARCHAR and TEXT values in PostgreSQL

postgresql,encoding,plpgsql,url-encoding
Are there any well-known PL functions/libraries for extending a PostgreSQL (9.4.1) database with URL encoding (also known as percent encoding) capabilities? Here's an example of the intended functionality: Input string: International donor day: give blood for a good cause! Output string: International%20donor%20day%3A%20give%20blood%20for%20a%20good%20cause%21 I guess an alternative would be to roll...

Select as JSON object {key: {}}

json,postgresql,plpgsql
My table: ID | something1 | something2 | ... 1 | meow | 5 | 2 | 4 | KITTIES | Is there any way to select data as JSON in format {"1":{"something1":"meow","something2":5},"2":{...}}?...

Dynamic function invocation in trigger

postgresql,function,database-design,plpgsql,check-constraints
In my use case I need to be able to execute a dynamic (predefined) function with static arguments after insertion into a table. Logically I'm thinking: Define function calls (i.e. name and static args)in their own table. Associate these static function call definitions with records in another table (insertion into...

Alter column bytea to text in various versions of Postgres with plpgsql

postgresql,plpgsql,alter,version-numbering
I have problem how to convert column using plpgsql (bytea -> text). I wrote function which works on some databases and doesn't for others. I don't know how to fix it. Using databases from 8.0 - 9.3; this error is for 8.1.19. I received: ERROR: column "the_column" cannot be cast...

iterate and write separately linestrings (generate_series)

sql,postgresql,postgis,plpgsql
I have a table full of a mix of polygons and multipolygons and I would like to run a single function on to break them into LineStrings (or MultiLinestrings). My problem - I am currently returning a set with ... no geometry (?)... The function currently looks like this...(final based...

Dealing with PostgreSQL %ROWTYPE returns

java,postgresql,jdbc,dao,plpgsql
I've got a PostgreSQL function which looks like this: CREATE FUNCTION myFunc() returns myTable as $$ DECLARE my_row myTable%ROWTYPE; BEGIN select * into strict my_row where...... return my_row EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'Blame the user !' USING HINT = 'Its always the users fault !'; END; $$ LANGUAGE...

plv8 disadvantages or limitations?

postgresql,plpgsql,plv8
I'm playing around with PLV8 to write trigger and stored procedures for PostgreSQL. So far I don't really see disadvantages compared to PLPGSQL. Especially if working with JSON it seems even smarter then PLPGSQL. Are there known disadvantages or limitations if using PLV8? Can PLV8 be a full replacement for...

FOR loop on PLpgSQL function result

sql,postgresql,plpgsql,postgresql-9.3
I wrote a PLpgSQL function which should return SETOF products table: CREATE OR REPLACE FUNCTION get_products_by_category (selected_category_id smallint DEFAULT 1) RETURNS SETOF products AS $BODY $BEGIN RETURN QUERY (SELECT * FROM products WHERE CategoryID = selected_category_id); END; $BODY$ LANGUAGE plpgsql VOLATILE NOT LEAKPROOF COST 100 ROWS 1000; And next I...

Update table in a complex function using exceptions

sql,postgresql,sql-update,plpgsql,postgresql-9.3
I'm little lost trying to solve a problem. At first I've this 5 tables: CREATE TABLE DOCTOR ( Doc_Number INTEGER, Name VARCHAR(50) NOT NULL, Specialty VARCHAR(50) NOT NULL, Address VARCHAR(50) NOT NULL, City VARCHAR(30) NOT NULL, Phone VARCHAR(10) NOT NULL, Salary DECIMAL(8,2) NOT NULL, DNI VARCHAR(10) UNIQUE, CONSTRAINT pk_Doctor PRIMARY...

Reference Local Variable In PL/PGSQL Dynamic SQL Inside Function

sql,postgresql,function,plpgsql,dynamic-sql
I have a PL/PGSQL function which is for data processing. I need to first select each row from a table, and then retrieve the column names and the associated values of each column. So basically I am un-pivoting the records to a horizontal state. This is necessary since they will...

Retrieve updated rows in AFTER UPDATE trigger Postgresql

postgresql,triggers,plpgsql
I'm writing a AFTER UPDATE trigger in postgresql. Actualy I need to get at least one row after update in STATEMENT LEVEL trigger, but there is no OLD or NEW variable there. In FOR EACH ROW trigger I didn't manage to find a sort of batch_last param. But to do...

How to get individual columns from table returned from a function?

database,postgresql,function,rows,plpgsql
I'm trying to make a function with returns table. CREATE FUNCTION karta_pacjenta(pe VARCHAR(11)) RETURNS TABLE('data' DATE,'imie' TEXT, 'nazwisko' TEXT, 'diagnoza' TEXT,'przepisany lek' TEXT) AS' BEGIN RETURN QUERY SELECT w.dzien AS dzien,p.imie, p.nazwisko, ch.nazwa, l.nazwa FROM pacjenci p, diagnozy d, choroby ch, wizyty w, leki l, recepty r WHERE p.pesel=d.pesel AND...

Escape tab character PostgreSQL

json,postgresql,escaping,plpgsql
I have got a varchar field and I am migrating this field to a JSON type field. Some of the data has got tabulator, and when trying to insert to the new JSON field it tells me this error: DETAIL: Character with value 0x09 must be escaped. I am trying...

How to use dynamic column names in an UPDATE or SELECT statement in a function?

sql,postgresql,parameter-passing,plpgsql,dynamic-sql
In PostgreSQL 9.1, PL/pgSQL, given a query: select fk_list.relname from ... where relname is of type name (e.g., "table_name"). How do you get the appropriate value for "relname" that can be used directly in an UPDATE statement as: Update <relname> set ... within the PL/pgSQL script? Using quote_ident(r.relname) as: Update...