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.

21 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
44 views

Need a Function that allows parameters to pass as argument for logic inside the function in Greenplum

Suppose I have a query :- select 'public.test123 is' || ' ' || quote_literal(obj_description('public.test123'::regclass)) || ';' as test; test ------------------------------------- ...
3
votes
0answers
403 views

pg_restore error: Extensions installed out of sequence

I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command: $ pg_dump -c -U postgres -Fc -f test.dump testdb When I ...
3
votes
0answers
237 views

Is it possible to create a user defined type with plpgsql or SQL?

Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C. Specifically I wanted to create a type like this CREATE TYPE name ( INPUT = ...
2
votes
0answers
269 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 ...
2
votes
0answers
290 views

Execution plan of query inside pl/pgsql function

Briefly, we have a pl/pgsql function which collects some intermediate data in several temporary tables ("on commit drop") and then returns REFCURSOR which joins those temporary tables with some static ...
1
vote
1answer
376 views

How to insert very big XML data file to PostgreSQL

I'm using PostgreSQL 9.5 and trying to import XML data file to database. To load the XML I use this function from here CREATE OR REPLACE FUNCTION bytea_import(p_path text, p_result out bytea) as $$ ...
1
vote
0answers
22 views

PostgreSQL multiple database dml

I want to insert same data in db_two.tab_emp table automatic by trigger or anything. create database db_one; create table db_one.tab_emp(id int, name text); create database db_two; create table ...
1
vote
0answers
92 views

Postgresql- how to rotate table partitions, trunc oldest every day?

I need to create a partitioned table with 4 rotating partitions for 4 days, and every day truncate the oldest partition. I know how to create table partitions on postgres, but I can't understand how ...
1
vote
1answer
553 views

How to specify with a parameter a field to extract from a RECORD variable

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type ...
1
vote
0answers
1k views

How to write a comma-separated list of a single scalar variable in PL/pgSQL?

This is related to: Postgres function assign query results to multiple variables https://stackoverflow.com/q/15949337/5419599 I want to loop over values returned in a single column. In other words: ...
1
vote
1answer
181 views

Create table from record

Is it possible to create a table from a record? For example: DO $$ DECLARE v1 RECORD; BEGIN SELECT 1 AS c INTO v1; CREATE TEMP TABLE temp AS SELECT v1.*; END; $$ LANGUAGE 'plpgsql'; This ...
1
vote
0answers
47 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
59 views

Pass 'interval' value to date_trunc function in PostgreSQL procedure

I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns. The syntax in the ...
0
votes
1answer
61 views

Loop Block with execution doesn't persist

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist. Like this... do $$ declare dados Record; begin for ...
0
votes
0answers
40 views

How Create a directory dynamically and output a json file to the directory with PL/pgsql?

I want to export updated records to json files. For this purpose I defined a trigger as follow: CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $BODY$ DECLARE directory text := 'dblogs/me....
0
votes
0answers
1k views

How can i return Postgresql temp table in POSTGRESQL

I'mn new in PostgreSQL here is my function which I created. but it does not run successfully because something I'm doing wrong. please, can anyone help me in this? CREATE OR REPLACE FUNCTION ...
0
votes
1answer
37 views

Call dynamically a function using plpgsql

I create a function that returns the value of a field when the id is given : CREATE OR REPLACE FUNCTION get_dcteam(id int) RETURNS text AS $$ #print_strict_params on DECLARE dc_team text; BEGIN ...
0
votes
1answer
132 views

Capture COPY & UPDATE command tag from an EXECUTE statement in a PL/pgSQL function?

When I populate a table from the psql terminal: COPY schema.table ( column_1 , column_2 , column_3 ) FROM 'file.csv' WITH (OPTIONS); the message: COPY x is displayed in the terminal if ...
0
votes
1answer
49 views

using PERFORM to insert a string of SELECT statement into a temp table

I am trying to insert data into a temp_table and then truncating the table after analyzing the result. Here is my code: CREATE OR REPLACE FUNCTION validation() RETURNS text AS $$ DECLARE counter ...
0
votes
1answer
124 views

CHECK constraint to limit certain rows

I have the task to create an EAV table with some constraints. Attributes are foreign keys and some values are also from other tables (not realized as foreign key, but as a CHECK constraint). On this ...
0
votes
1answer
27 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 ...