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
453 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
250 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
119 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 ...
2
votes
0answers
280 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
299 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
0answers
23 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
1answer
709 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
2k 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
213 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
50 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
17 views

Function does not exist, but im pretty sure it does

So, i have a procedure.. create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp) as $$ begin update sensor set last_read = read_time where ...
0
votes
1answer
190 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
70 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
42 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
2k 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
38 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
135 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
50 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
134 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
28 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 ...