What's the deal with Deno? We talk with a major contributor to find out. Listen now.

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.

Filter by
Sorted by
Tagged with
0
votes
2answers
25 views

How to use WITH inside IF statement in PostgreSQL

I am writing a function that accepts a text flag based on which I need to run some code. So I am using an IF clause. However the code that has to execute inside IF and ELSE is a recursive CTE which ...
4
votes
1answer
5k 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 ...
1
vote
1answer
948 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 ...
0
votes
0answers
29 views

How to get TABLE like dynamic structure output/returned from PostgreSQL function?

I am not very experienced with PLPGSSQL hence seeking some guidance to achieve following... I have an Oracle Procedure which i have to convert to PostgreSQL. This oracle function has declared a TABLE ...
-2
votes
1answer
38 views

postgreql 11: how to get result of a query in set of records and then process them in a function

I want to write a function like below.Whats wrong in it? CREATE OR REPLACE FUNCTION myFunc(abc BIGINT, sss myTable.Col1%Type) AS DECLARE myRec RECORD; tab_Rec RECORD[]; --This i want to as a table (...
0
votes
1answer
100 views

Stored Procedure for Vacuum in PostgreSQL

I want to create a stored procedure to trigger vacuum, I know there are many ways to automate vacuum, but I would like to use the stored procedure (Postgresql 9.6). The problem Im facing is, I can't ...
0
votes
0answers
21 views

PL/PGSQL - Inner Join in Foreach loop

So I have a table, report_items with a structure as follows: id (uuid) | date (date) | sheet (text) | report_item (text) | report_value (numeric) | stock_id (uuid) | period (text, either quarterly or ...
5
votes
2answers
14k 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
1answer
27 views

PLPGSQL: Cannot use parameters inside a function query

I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator. I have tested that the ...
0
votes
1answer
133 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 ...
43
votes
7answers
39k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
0
votes
1answer
59 views

“invalid transaction termination” in Postgres when trying to update a value using a function

I'm new to postgres. I defined a function like this: CREATE OR REPLACE FUNCTION increment_count() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN UPDATE posts SET count=count+1 WHERE pid=...
0
votes
1answer
27 views

postgresql: mapping json array to string array

I have following PostgreSql function: CREATE OR REPLACE FUNCTION "fn_get_list_invoice_number_by_product_ids"(_product_ids INT[]) RETURNS "pg_catalog"."json" AS $BODY$ ...
36
votes
1answer
21k 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?
0
votes
0answers
18 views

PostgreSQL functions for creating and dropping declarative partioning

I created function for creating declararative partitions. CREATE OR REPLACE FUNCTION createpartition(table_name varchar(25), forecast_id integer) RETURNS varchar(30) AS $$ DECLARE partition_name ...
1
vote
1answer
311 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 ...
0
votes
1answer
667 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
0answers
21 views

Trigger having issues on partioned table execution - Postgres - 11

Postgres - Trigger having issues on partitioned table execution I am using postgres 11 version I am trying to execute a trigger on partitioned table with customized function and trigger function, that ...
9
votes
2answers
15k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
19
votes
2answers
7k views

Postgres query plan of a function invocation written in plpgsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed inside a user defined function (UDF) using EXPLAIN. So how do I get hold of the query plan for ...
2
votes
1answer
49 views

Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column ...
2
votes
1answer
6k views

How to create tablespace if it does not exist

When I create a new schema in Postgres through executing a script in psql I want to put it in a tablespace. If this tablespace does not exist then I want to create it first. Since the default SQL does ...
0
votes
1answer
47 views

Bulk Inserts in Postgres

This is with respect to Data Migration activity where the historical data from the client database is migrated to vendor Postgres Database. There will be millions of transactions that need to be ...
1
vote
2answers
75 views

When (or why even) use PLPython(3)u

As I gain more experience with PostgreSQL I start to question the existence of PLPython. It's considered an "untrusted" language https://www.postgresql.org/docs/10/plpython.html What I am ...
1
vote
1answer
665 views

Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can ...
0
votes
1answer
28 views

How do I loop through every table and alter the PK column like so? (Postgres)

Using psql in Postgres 12, I need to alter every column in every table of my database that's: int/bigint not null primary key to: identity column auto-increment with default value starting at ...
0
votes
1answer
34 views

Postgres function/procedure won't return value

I'm new to PostgreSQL, coming from SQL server and I'm having trouble creating a function and/or procedure. First of all, may I just clarify that the only difference between a function and a procedure ...
0
votes
3answers
41 views

Postgres PLPGSQL 'un-stored' procedures?

I'm getting the following error when I run the query below: ERROR: query has no destination for result data Hint: If you want to discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL ...
0
votes
0answers
110 views

Optimizing implementation of custom ULID generation in Postgres

Following the spec github.com/ulid/spec I'm trying to implement this on Postgres, with some tweaks (without monolitic implementation, no base32): 43 bits allocated to timestamp instead of 48 85 bits ...
0
votes
0answers
18 views

How to preform a query as argument in PL/pgSQL function which returns JSON?

I'm trying to perform select by using already created function which returns JSON which aggregates into JSON array, but I'm getting problem in line where I'm performing a SELECT-clause. Tables I have ...
3
votes
1answer
221 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
2answers
121 views

42702 'column reference “id” is ambiguous' for RETURNING of table-returning PL/pgSQL function

This works: CREATE OR REPLACE FUNCTION sql_fun() RETURNS TABLE (id UUID) AS $$ INSERT INTO foo DEFAULT VALUES RETURNING id $$ LANGUAGE SQL; SELECT * FROM sql_fun(); This doesn't: CREATE OR ...
0
votes
1answer
23 views

Create function with a parameter, which will become the part of IN (1, 2, 3) clause

Following is the part of function declaration. Unfortunately it doesn't work the way I would like it to. CREATE FUNCTION my_cool_func(some_parameter INTEGER, some_other_parameter INTEGER[]) RETURNS ...
10
votes
1answer
17k views

Getting a random row from PostgreSQL?

I want to get a random row from my table by id. My table: ID|Word |Dificult|Category_id| 1 |'Dumb' |'Easy' | 3 | 2 |'Leopard'|'Medium'| 6 | If my user selects a category and ...
0
votes
1answer
52 views

relation “table” does not exist

i created the following schema , inside that schema i created a table and inserted a some values. After that i created a function that get a value in the table based on an ID. Here is all the ...
-2
votes
1answer
38 views

SQLServer script to Postgres script conversion [closed]

I want to convert the following stored procedure from SQL Server to Postgres This script returns multiple records. Please help me regarding this. CREATE procEDURE [dbo].[...
1
vote
1answer
53 views

Set var=“NEW.this_word_column” in a trigger function if “word” is present in a column name

Context I'm using PostgreSQL 10.12. I want to be able to fire a trigger function on some specific tables with geographic data. This trigger function is designed to create a PotGIS point into a ...
-1
votes
1answer
30 views

Help with calculating a sum of all previous rows in each subsequent row in postgresql

I have the following view which calculates the percentage increase of the site resources used for each date per site id: What I'm trying to accomplish, is to add an extra column which will show the ...
1
vote
1answer
36 views

Assign a value to a field of a composite type (a row in an array of rows)?

I am currently writing a function that works with an array of rows. I need to write a value to a particular column of a particular row in the array. The following code should encapsulate the problem I'...
8
votes
3answers
42k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
0
votes
1answer
80 views

Postgres functions: how to pass a type as an argument

I am using this query to list the values that are part of an enum: SELECT unnest(enum_range(null::my_enum_name)) AS enum_values; It works. I am trying to make it more reusable by wrapping it in a ...
0
votes
1answer
16 views

Are triggers and their results visible in same order as original operations?

I've created simple trigger CREATE TABLE data ( id int, v1 text, v2 text ); CREATE TABLE events ( id serial primary key, v1 text ); CREATE OR REPLACE FUNCTION emit_event_for_v1()...
0
votes
2answers
166 views

ROLLBACK TO SAVEPOINT syntax error at or near “TO”

I am having trouble in executing the following FUNCTION in PostgreSQL CREATE OR REPLACE FUNCTION pckg_fcvoi.supprimer_voies_plus_42c (CodeEntite text) RETURNS VOID AS $body$ DECLARE ...
0
votes
0answers
29 views

Dynamically creating a new table from a given table name without return type using a PostgreSQL function?

I want to try using PostgreSQL to create a function which given a table name, an existing column name, and a new column name, it selects all data from this table, applies a function to this column ...
4
votes
1answer
2k 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 ...
1
vote
2answers
57 views

How to avoid multiple SELECTs from a bag of properties table?

I have a table with key-value pairs for managing user settings. This approach is known as the “bag of properties” and one of its benefits is you don’t need schema changes when a new property is ...
0
votes
1answer
45 views

Issue of structure of query does not match function result type

"structure of query does not match function result type" this isssue occurred when i try to execute the this function. CREATE OR REPLACE FUNCTION historicalavg01() RETURNS SETOF text AS $$ DECLARE ...
3
votes
1answer
143 views

Custom error code class and numbers for Postgres stored functions

I'm writing a collection of stored functions in Postgres 11.5, and want to RAISE EXCEPTION when various pre-conditions are not met. For example, a null or empty string with a string is required, or an ...
-1
votes
1answer
88 views

How to return value from function in PostgreSQL [closed]

I have a function in postgreSQL, I want that if the query in function executes properly then it should return true else false example: CREATE OR REPLACE FUNCTION enquiry."fn_queryFunction"( IN ...
5
votes
3answers
45 views

Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?

I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...

1
2 3 4 5
7