We’re rewarding the question askers & reputations are being recalculated! Read more.

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.

Filter by
Sorted by
Tagged with
2
votes
1answer
185 views

Postgresql trigger update whole table after delete

I have a table "measurements" which looks like this (with empty cells in the last col) ser_nr | meas_ser | meas_value | last_meas -------+----------+------------+----------- A1 | 1 | 12 ...
-4
votes
1answer
351 views

PostgreSQL function incorrectly returns 0 rows [closed]

I'm learning how to create functions in Postgres. Below is a function I'm trying to get to return two numbers, but no matter what I try it always returns 0 rows. Does anyone have any idea how I could ...
1
vote
1answer
2k 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 ...
3
votes
1answer
922 views

Array of template type in PL/pgSQL function using %TYPE

I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE). I want to update it so one of those arguments is ...
3
votes
2answers
3k views

Why does 'plpgsql' not appear in the pg_extension table?

I have two Postgres 9.1 installations. One works properly, the other does not. On the working installation: dc=# select * from pg_extension; extname | extowner | extnamespace | extrelocatable | ...
1
vote
1answer
149 views

Postgres - Get the query cost assign to a variable

To get the estimated cost of the query, i using EXPLAIN SELECT column FROM table;, to get the current cost of the query, i am using EXPLAIN ANALYSE SELECT column FROM table;, my question is how to get ...
1
vote
1answer
4k views

How to create tablespace if it does not exist

When I create a new schema in Postgres through executing a script in psql I want to put it in a tablespace. If this tablespace does not exist then I want to create it first. Since the default SQL does ...
1
vote
2answers
140 views

“Lambda” function returns type string instead of type record

I am using PostgreSQL 9.5.4 and trying to create a lambda function based on this code: https://github.com/decibel/pg_lambda/blob/master/sql/lambda.sql set client_min_messages to debug; create or ...
1
vote
1answer
327 views

PostgreSQL function to get levels (hierarchy)

I need a function which returns description of level from cascade (hierarchy) tables. CREATE TABLE level_1( id_1 SERIAL, description_1 text ); CREATE TABLE level_2( id_2 SERIAL, description_2 ...
1
vote
2answers
16k views

RETURN value directly from INSERT with RETURNING clause

I have a function that ends with: INSERT INTO configuration_dates ( cols... ) VALUES ( values... ) RETURNING id INTO ret_id; RETURN ret_id; And I would like to remove the ...
16
votes
1answer
5k 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') ...
1
vote
3answers
713 views

How to speed up string cleanup function?

I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced. I am new to Postgres. My function ufn_cie_easy() performs way too slow: DECLARE ...
3
votes
1answer
4k views

Assignment of a column with dynamic column name

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following: CREATE OR REPLACE FUNCTION ...
1
vote
1answer
2k views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
2
votes
1answer
1k views

How to insert a record returned by a function into a table

I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record. CREATE FUNCTION my_func( IN id bigint, OUT foo bigint, OUT bar double precision ...
10
votes
2answers
4k views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
1
vote
1answer
153 views

PostgreSQL - cannot create RULE from stored procedure

I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue ...
0
votes
1answer
611 views

plpgsql function to replace ' ' with null after insert using a trigger

I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was ...
4
votes
1answer
36 views

Is there an easier way to show why this query is always equivalent to bitor?

This works, and I can't see right off the bat why: CREATE OR REPLACE FUNCTION bitor ( x bigint, y bigint ) RETURNS bigint AS $body$ BEGIN RETURN x + y - bitand(x,y); END; $body$ LANGUAGE PLPGSQL ...
2
votes
2answers
8k views

Assign values with the := operator

When I assign a variable with result := title || '', by '' || author; it is taking more time (approx. 15 sec) to run the function. However, when I assign the variable with result = title || '', by '...
4
votes
1answer
460 views

Persistent inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
0
votes
1answer
513 views

Trigger function - PostgreSQL 9.2

The idea: 1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job I am able to do that, using this SQL: ...
1
vote
1answer
85 views

Trigger for a particular row?

I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like: CREATE ...
2
votes
1answer
5k views

Wildcard search using parameters in function with dynamic SQL

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter ...
2
votes
1answer
524 views

Dynamic Column Label in Postgres 9.5

I am using postgres 9.5 and have built the following function: CREATE or REPLACE FUNCTION func_getratio_laglag(_numeratorLAG text, _n1 int, _denominatorLAG text, _n2 int, _table text) RETURNS ...
0
votes
1answer
5k views

syntax error at or near “IF” [closed]

I try to create a sp that checks if a job that didn't started already exists and if not creates a new one. It should return the id of the job. For now I have the following code: CREATE OR REPLACE ...
15
votes
2answers
44k views

Is there a simple way in PL/pgSQL to check if a query returned no result?

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this: select c.data into data from doc c where c.doc_id = id and c.group_cur > ...
2
votes
1answer
2k views

PostgreSQL - is it possible to use a plpgsql function in a bash script?

Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up). Problem : Since between the partial-backup ...
1
vote
1answer
2k views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
1
vote
1answer
185 views

Plpgsql (Postgres) function for sanity check

While starting up my app, I need to do a sanity check within my database. In a table sensor, there's a column seconds. Now the function should take an argument id. It then should simply do something ...
6
votes
4answers
4k views

Copy indexes from one table to another

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
4
votes
1answer
3k views

PL/pgSQL issues when function used twice (caching problem ?)

I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem. I have this function: CREATE FUNCTION sp_connect(mail character varying, passwd character ...
3
votes
1answer
4k views

Basic transactional DDL script in PostgreSQL

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
2
votes
1answer
316 views

Postgres plpgsql: Is a view better than a select query in a plpgsql function

I have a recursive query to the general effect of: WITH RECURSIVE text1 (selfid, parentid, text) AS ( SELECT DISTINCT text.selfid, text.parentid, text.text FROM text WHERE text.selfid = ...
0
votes
1answer
3k views

How to return a set of rows from this function?

I am new to Postgres functions and have a table with the following structure: CREATE TABLE options.options ( delta double precision, gamma double precision, rho double precision, theta double ...
1
vote
0answers
50 views

xmlforest() omits any fields that are null entirely, instead of creating an “empty” element

I'm know I can use an empty string, but I don't want to lose the null functionality in the database. In some cases null is appropriate and I can't use empty strings. Other than a empty string, how ...
0
votes
1answer
69 views

syntax error on mathematical operation postgresql

I'm trying to declare this function to be used as a trigger: create or replace function atualiza_qtfunc() returns trigger as $f_at_qtfunc$ --f_at_func = função atualiza quantidade funcionários ...
4
votes
1answer
3k views

Create a function from another function

Is it possible to create a new function from another plpgsql function? Something like this: CREATE OR REPLACE FUNCTION func_test() RETURNS VOID AS $BODY$ BEGIN CREATE OR REPLACE FUNCTION func_test2(...
1
vote
2answers
10k views

Postgresql - Error in function that returns table

I just defined this function to return all column names from a given table: create or replace function GET_COLUMNS(in tbl_name character varying(30)) returns table(colunas character varying) as $$ ...
1
vote
1answer
541 views

Make query depend on input in PostgreSQL function

I have a simple query: CREATE OR REPLACE FUNCTION companies_all_teams( IN _company_name text = NULL, IN _company_id int = NULL, OUT _result text ) AS $$ BEGIN _result := (SELECT json_agg(...
1
vote
1answer
443 views

How do I in “Navicat for PostgreSQL” run a simple PL/pgSQL script like I would in MS SQL Server Management Studio?

I don't want a function or anything. I just want to run/test a simple script with some PL/pgSQL features in it. Something like the following: IF 1=1 THEN SELECT concat('Hello', 'World'); END IF; ...
0
votes
1answer
71 views

How simplify/optimize a search of keywords in two tables at the same time?

I'm trying to model a function which will be used by a search engine (of cars for the example). The last version of PostgreSQL will be installed on a Windows server (I don't know server's ...
2
votes
1answer
6k views

How to use values from table column as field names in SELECT query?

Scenario: Field names of Table1 named as testtable id,name,size,width,height Field names of Table2 named as errortable id,desc,field1,field2,operator Values of errortable +----+----------------...
0
votes
1answer
28 views

First field from my return table is being eaten up

Another problem with some PostgreSQL stored procedure code... Same from this question: I have this function drop function uptimes(inicio date, fim date); create or replace function uptimes(inicio ...
4
votes
2answers
703 views

How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function). But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy ...
2
votes
0answers
280 views

Postgresql: Limits of tsquery field

I'm using a tsquery field in order to pre-compute batch searches, but I'm encountering this error while updating a tsquery field on a table: ERROR: row is too big: size 10704, maximum size 8160 The ...
8
votes
1answer
27k 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; ...
40
votes
3answers
44k 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 ...
2
votes
1answer
2k views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
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/...