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

1
vote
1answer
38 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
32 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 ...
1
vote
1answer
23 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 ...
8
votes
2answers
2k 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 ...
4
votes
1answer
745 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 ...
0
votes
1answer
26 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
21 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 ...
2
votes
2answers
156 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: ...
1
vote
0answers
10 views

Plpgsql function array plus replace

Please be indulgent to my question, 'cause I am newbee at plpgsql/sql. :) I need to do a replace in table and the insert 'replaced' data in the same table. How to replace some values using array with ...
4
votes
1answer
23 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
71 views

PL/pgSQL regclass quoting of table named like keyword

I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new ...
2
votes
2answers
3k 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 ...
3
votes
1answer
55 views

Converting SQL SERVER Procedure to POSTGRESQL

I have procedure in SQL Server to print all players of given country. I try to convert it into plpgsql, but I get errors. SQL Server Procedure CREATE PROCEDURE kraj_pilkarze @kraj varchar(30) AS ...
4
votes
1answer
90 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
110 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
35 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
118 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
66 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
95 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 ...
27
votes
4answers
14k 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
181 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
15k 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
263 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
103 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
47 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 ...
3
votes
4answers
378 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
297 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
123 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
26k 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
230 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
111 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
55 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 = ...
7
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 error....
0
votes
1answer
215 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
15 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
80 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
36 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
82 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
141 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
50 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
79 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
45 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
185 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
313 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
112 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
122 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
86 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
203 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 T-...