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
0answers
14 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
24 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 ...
2
votes
1answer
24 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; ...
1
vote
1answer
37 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 ...
1
vote
1answer
45 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
21 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 ...
3
votes
2answers
65 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 ...
1
vote
1answer
47 views

Upsert statement never completes, infinite loop?

Edit: The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a ...
2
votes
1answer
72 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
147 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 ...
1
vote
3answers
118 views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...
0
votes
1answer
40 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: ...
1
vote
2answers
42 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 ...
1
vote
2answers
114 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
1
vote
1answer
126 views

Conditonally create temporary table

Invoking the following SQL leads to a warning 'column "s" has type "unknown"' which I can understand. What I'm trying to accomplish is to conditionally create a temporary in preparation of running a ...
2
votes
1answer
102 views

Is this inconsistent treatment of `void` return from sql and plpgsql functions documented?

begin; create table foo(id integer); create function f1() returns void language sql as $$delete from foo;$$; create function f2() returns void language plpgsql as $$begin delete from foo; end;$$; ...
1
vote
2answers
203 views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: ...
3
votes
2answers
114 views

PL/pgSQL fuction to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
0
votes
1answer
266 views

Assign result of regexp_split_to_array() to Pl/pgSQL array variable

How do I create an array and then put a result of a function into it? I'm doing it like this: array text[]; select regexp_split_to_array('whatever this is it splits into array with spaces', E'\\s+') ...
-1
votes
1answer
124 views

Access table field on creating a trigger postgresql

How can I access table field on creating a trigger? CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
0
votes
1answer
171 views

ERROR: loop variable of FOREACH must be a known variable or list of variables

I have few FOREACH loops in my functions. The loops seem to perfectly agree with the official postgresql documentation. This error message is returned: ERROR: loop variable of FOREACH must be a known ...
1
vote
1answer
409 views

I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation ...
2
votes
1answer
82 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
0
votes
1answer
758 views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
1
vote
1answer
72 views

RETURN NEXT in Postgres Function

I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data. I was originally going to return an array, but I found a resource that explains the ...
2
votes
2answers
899 views

Postgresql: Query across schemas

I have multiple schemas that I need to query across. The schemas are not know in advance for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
1
vote
2answers
242 views

How to do WHERE x IN (val1, val2,…) in plpgsql

I would like to update a selection of rows in a table; this works... UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5); But how can I do the same in a plpgsql function? The following gives a ...
4
votes
1answer
203 views

How to access NEW or OLD field given only the field's name?

I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
1
vote
2answers
142 views

Basic trigger in PL/pgSQL, to flip a boolean field if another field has text

I want a BOOLEAN field to be set to TRUE when a sibling TEXT field is set to a non-empty string. Vice versa, the BOOLEAN is set to FALSE when TEXT field is set to empty string. The following function ...
1
vote
1answer
149 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
249 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 ...
3
votes
2answers
397 views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...
1
vote
1answer
377 views

How to get query runtime in PL/pgSQL to be as fast as console query runtime?

Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ...
3
votes
1answer
456 views

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
3
votes
2answers
625 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 ...
3
votes
1answer
4k views

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line ...
1
vote
1answer
850 views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for ...
2
votes
2answers
2k 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 $$ ... $$ ...
2
votes
0answers
78 views

How can I use an environment variable in a Postgres function?

Let's say I have a windows user environment variable SOME_STRING. Is it possible to use its value in a PL/pgSQL function? If not, do I need to use an untrusted language (like PL/pythonu) or can I ...
7
votes
1answer
1k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
1
vote
2answers
471 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 ...
2
votes
2answers
258 views

Trigger update does not seem to finish

I'm new to PostgreSQL triggers & functions and such. I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update ...
7
votes
2answers
782 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 ...
3
votes
1answer
1k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
1
vote
1answer
832 views

PostgreSQL stored function that returns arbitrary resultset

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
2answers
2k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
3
votes
1answer
358 views

How to turn off all RAISE NOTICE on production server?

I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those ...
1
vote
1answer
2k views

How to use array variable in query in PostgreSQL

Create table t1 ( xcheck varchar[], name text ); CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[]) RETURNS record AS DECLARE xrc as record; execute 'select name from t1 where xcheck @> ...
1
vote
1answer
2k views

Error: set_valued function called in context that cannot accept a set. What is it about?

I use Postgresql 9.1, with ubuntu 12.04. Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
1
vote
1answer
918 views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...