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
1answer
87 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: ...
4
votes
1answer
657 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 ...
1
vote
1answer
28 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 ...
1
vote
1answer
57 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
2answers
45 views

Use array of composite type as function parameter and access it

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table. This is my type: ...
2
votes
1answer
54 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 ...
1
vote
1answer
59 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 ...
26
votes
4answers
13k 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 ...
0
votes
1answer
50 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 ...
9
votes
2answers
14k 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
2answers
188 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
83 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
42 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 ...
2
votes
4answers
204 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 ...
3
votes
1answer
113 views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS ...
3
votes
1answer
81 views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
34
votes
2answers
24k 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 ...
4
votes
1answer
150 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 ...
2
votes
1answer
58 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
42 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 = ...
6
votes
2answers
3k 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 ...
4
votes
1answer
62 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
112 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 ...
2
votes
2answers
8k 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
0answers
12 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 ...
4
votes
2answers
68 views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
0
votes
1answer
35 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 ...
2
votes
1answer
61 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 ...
1
vote
2answers
99 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
43 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 ...
1
vote
1answer
65 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
43 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 ...
1
vote
1answer
107 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 ...
2
votes
1answer
184 views

“ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
0
votes
1answer
18 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 ...
1
vote
1answer
88 views

Find unmatched rows between two tables dynamically

I have a function here that is supposed to take two tables as arguments and check if they are the same. create or replace function testing.equal_tables( varchar, varchar) returns void as ...
4
votes
2answers
97 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
65 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 ...
-4
votes
1answer
117 views

How to Pass table type as Parameter in T-Sql like Postgresql [closed]

In my company like to move from PostgreSQL to SQL Server. Doing this, we need to migrate pl/Pgsql functions to T-Sql. In pgsql function we are create and pass type as parameter. Same option had in ...
4
votes
1answer
9k 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; ...
34
votes
3answers
30k 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
258 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
574 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. ...
3
votes
3answers
398 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 ...
3
votes
1answer
80 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
177 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 ...
3
votes
3answers
146 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 ...
6
votes
3answers
1k 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
56 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
453 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++){ ...