Stack Overflow
Stack Overflow

Questions tagged [plpgsql]

PL/pgSQL is the default procedural language for PostgreSQL. Questions about PL/pgSQL should probably be tagged [PostgreSQL] as well.

PL/pgSQL is the default procedural language for PostgreSQL. It can be used to write user defined functions and trigger functions. Since PostgreSQL 9.0, PL/pgSQL is installed by default. It can be used in anonymous code blocks using the DO statement, in functions and in procedures (since Postgres 11).

Reference:

4245 questions
1
vote
1 answer

Unpacking and using JSONB data as a PL/PgSQL function on 11.7

I'm working towards a function that takes a pair of JSON arrays and some setting values, and then makes comparisons between the items in the arrays. Leading up to that, I'm trying to get the JSONB parsed within PG/PlSQL. This version with hard-coded…
Morris de Oryx
  • 1,857
  • 10
  • 28
1
vote
1 answer

Expanding a JSON array embedded in an object in Postgres 11.7

Postgres 11.7. An upgrade to PG 12 (JSONPath, I know) is in planning stages, not sure when we'll get there. I'm working on passing some data to a PL/PgSQL stored function, and am struggling to unpack an array embedded in an object. I'm dealing with…
Morris de Oryx
  • 1,857
  • 10
  • 28
1
vote
0 answers

why am i getting an error in this for each in PL/pgSQL

I have this function applying jaccard index in some text strings CREATE OR REPLACE FUNCTION jaccard(INPUT text, INPUT2 text) RETURNS real LANGUAGE plpgsql AS $$ DECLARE trigrama1 text[]; trigrama2 text[]; union text[]; interception…
Shai Jesus Gallegos Espinosa
  • 11
  • 1
1
vote
1 answer

Necessary to use variable in postgres plpgsql function to get row count of previous query?

In plpgsql functions you can use IF (FOUND) to find how many results the previous query had. Is there an equivalent to get the number of rows from the previous query, something like IF (ROW_COUNT > 5)? Or do you always need to use a variable to…
user779159
  • 9,034
  • 14
  • 59
  • 89
1
vote
1 answer

Using a variable on a PostgreSQL function to drop a schema

I'm trying to create a function on PostgreSQL, and I have some problem to use a local variable. Here's my code : DECLARE query RECORD; DECLARE schema_name TEXT; BEGIN FOR query IN SELECT * FROM context WHERE created_at + make_interval(days…
Gigs
  • 199
  • 1
  • 12
1
vote
1 answer

How to perform cascade rounding in PostgreSQL?

Cascade rounding is an algorithm to round an array of floats while preserving their sum. How does one implements this algorithm in PostgreSQL?
ido klein
  • 137
  • 2
  • 11
1
vote
1 answer

store result of execute string into temp table

Within a function I have created a string containing a Select Query as SQLSTR:='select col1,col2 from '||_param1||'_'||_param2||' where col1 like ''%'||_pram3; What I want is to store the result of SQLSTR into temporary table as FilterTable when…
Jay Mehta
  • 13
  • 3
1
vote
2 answers

Query on Return Statement - PostgreSQL

I have this question, I was doing some migration from SQL Server to PostgreSQL 12. The scenario, I am trying to accomplish: The function should have a RETURN Statement, be it with SETOF 'tableType' or RETURN TABLE ( some number of columns ) The body…
sys
  • 330
  • 3
  • 15
1
vote
1 answer

How to make a postgres function reorder rows representing a linked list?

I have a table like this that represents a linked list. When comes_after column is null it means it's the first record in the linked list. id | comes_after --------+------------ "one" | null "two" | "one" "three" | "two" "four" |…
user779159
  • 9,034
  • 14
  • 59
  • 89
1
vote
2 answers

Get data of multiple inserted rows in one object using trigger in postgres

I am trying to write a trigger which gets data from the table attribute in which multiple rows are inserted corresponding to one actionId at one time and group all that data into the one object: Table Schema actionId key value I am firing…
shruti garg
  • 177
  • 1
  • 14
1
vote
1 answer

How can I catch exceptions within a postgresql function?

I am initializing functions as part of an ETL pipeline; however, one of the functions is dependant to operate on a certain table, which has not yet been created by the time this function has been initialized. Illustration: CREATE OR REPLACE…
alt-f4
  • 2,112
  • 17
  • 49
1
vote
1 answer

How to recursively return table in postgres function using plpgsql? Without using CTE/With Recursive

I tried implementing it here as follows create or replace function getTextEditRecord(textId integer) RETURNS Table ( text_id integer, text_details character varying, new_text_id integer) AS $$ DECLARE curr_rec record; temp_rec…
Sanyam Madaan
  • 139
  • 4
  • 14
1
vote
1 answer

How to not execute INSERT in read-only transaction

Postgres server is in hot standbuy mode. Asynchronou streaming binary replication is used. Command like INSERT INTO logfile (logdate) values (current_date) Causes error cannot execute INSERT in a read-only transaction. Maybe it should be changed…
Andrus
  • 26,339
  • 60
  • 204
  • 378
1
vote
1 answer

Django MPTT - tree filtering

I am using MPTT's templatetag to render my genre tree. {% for genre, structure in genres|tree_info %} {% if tree.new_level %}
  • {% else %}
  • {% endif %} {{ genre.name }} {% for level in tree.closed_levels…
ohnoes
  • 5,542
  • 6
  • 34
  • 32
1
vote
1 answer

creating 512 partitioned tables with loop for postgresql

I am creating a table which would have 512 partitions in PostgreSQL 12. The table will be partitioned using hashing. How can I create the 512 partitions without having to create each one manually? Would creating a loop using pl/pgsql would be the…
david
  • 997
  • 3
  • 14
  • 34
1 2 3
…
99
100