All Questions
75 questions
0
votes
2
answers
195
views
Function performance is worse than raw query in PostgreSQL
I am trying to squeeze out the all the performance from my PostgreSQL database, also I want to abstract my query definitions from my application layer.
To do that, I am using table-valued function. ...
- 3
0
votes
1
answer
70
views
Split lines against polygons in a PostGIS trigger
I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of ...
- 1
2
votes
1
answer
216
views
How to define a function accepting exactly three characters?
I tried this:
create or replace function c3 (arg char(3))
returns void
language plpgsql as
$$
begin
raise notice 'Value: %', arg;
end;
$$;
But it accepts more than three characters:
select c3('...
- 359
1
vote
2
answers
813
views
PL/pgSQL select statement inside trigger returns null using where NEW
I'm creating a trigger that triggers ON INSERT to a table, and I wish to log the structure of tables inserted so I wrote this function:
CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS ...
- 46
0
votes
1
answer
253
views
Alter a function in Postgres 12 to allow for dates to be default or populated [duplicate]
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 459
0
votes
2
answers
4k
views
Default values for function parameters
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 459
0
votes
2
answers
394
views
I'm trying to make an SQL injection in my own function in PostgreSQL 13
Just for learning purposes, I'm trying to create a function using PLPGSQL and make an SQL injection on it. I recently learned about format, USING and quote_literal and quote_indent, so I'm good about ...
- 136
0
votes
1
answer
3k
views
Dynamic CREATE TABLE AS with indexes
I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process).
One of the things I need to move are a bunch of stored procedures, that generate tables ...
- 35
3
votes
1
answer
2k
views
Column name as argument for a trigger function
In my Postgres 14 database, I have a function that updates the updated_at field in a table for which the trigger was fired:
CREATE TEMPORARY TABLE types(
id SMALLINT GENERATED ALWAYS AS IDENTITY,
...
- 133
1
vote
2
answers
471
views
Variadic functions and composite types
I have a function like so:
update_stuff_variadic(
VARIADIC _stuff_array stuff[]
)
I understand that this syntax requires _stuff_array to match the column structure of the stuff[] table.
Instead ...
- 37
2
votes
1
answer
2k
views
PLPGSQL store columns of a 2D query output into array variables
In some step of a plpgsql function I need to store a 2D query result into array variables.
The following code does the job for scalars (but fails with arrays):
SELECT col_a, col_b
FROM my_table
WHERE ...
- 297
0
votes
3
answers
2k
views
How to return a number surrounded with parenthesis as a negative number
I have a table
CREATE TABLE tab1 (col1 integer, col2 character varying(20));
Col2 contains numeric value but the negative values corresponds with parenthesis not and minus (-). This is the sole ...
- 48
2
votes
1
answer
5k
views
COMMIT works in one plpgsql code block, but not in another?
Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...
- 163
0
votes
2
answers
3k
views
How to make a function in postgresql with two parameters to return count from column table?
I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year.
The table is something like this:
CREATE TABLE orders (
id INT NOT ...
- 37
1
vote
2
answers
914
views
Should Postgres function return/coerce numeric scale/precision?
The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function ...
- 121
0
votes
1
answer
4k
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=...
- 103
0
votes
1
answer
5k
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 ...
- 185
3
votes
2
answers
5k
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 ...
- 317
0
votes
1
answer
5k
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 ...
- 29
1
vote
2
answers
5k
views
Trigger function does not exist, but I am pretty sure it does
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 ...
1
vote
1
answer
3k
views
In PLPGSQL assign a variable based on result of SQL function
I have a PLPGSQL function which receives an address in two parts:
num = '123'
location = 'main street, anytown, KS'
To utilize the various portions of location I found there is a SQL function ...
- 13
1
vote
1
answer
816
views
Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?
I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists).
An example ...
- 13
0
votes
1
answer
2k
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 ...
1
vote
1
answer
466
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 ...
- 11
2
votes
1
answer
7k
views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
- 311
3
votes
3
answers
4k
views
Pass array of mixed type into stored FUNCTION
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.
the problem here is, that one argument is list of n ...
- 151
7
votes
1
answer
34k
views
How to use variables in procedure (not function)? [closed]
As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:
declare a variable
select a value into a variable
insert the variable into ...
5
votes
1
answer
2k
views
PostgreSQL UDF (User Defined Functions) overhead
Disclaimer
The task may seem esoteric, but nevertheless I want to create a POC of some sorts.
The goal
My goal is to make PostgreSQL database (version 10) expose an API to an application that uses it.
...
- 53
12
votes
1
answer
6k
views
SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?
Is there a difference between a plain SQL function doing a SQL query:
create function get_sports() returns setof sport as
$body$
select * from sport;
$body$
language sql stable;
and PLPGSQL ...
- 369
3
votes
0
answers
56
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
-------------------------------------
...
0
votes
1
answer
3k
views
How to get newly inserted row ids when inserting with execute?
I have a function which returns newly inserted row count:
BEGIN
EXECUTE (
SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
_tbl, ...
- 125
5
votes
3
answers
2k
views
Trigger function to update column
I'm a newbie to PL/pgSQL ... I use Postgres 9.5.0, and need to update a column every time a new record is inserted. The column shall be filled in from the values entered in area_pol and area_ofi.
I'm ...
2
votes
1
answer
2k
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,815
1
vote
1
answer
837
views
Function taking a string as table and column name
I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table:
CREATE OR REPLACE FUNCTION survey_participated(_sn text)
...
- 145
4
votes
1
answer
5k
views
Pass Parameters to trigger function to execute dynamic SQL
I have this code which was solved by Erwin:
Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function
Now I want the trigger to call the trigger function insert_data_func with parameters (...
- 85
2
votes
1
answer
3k
views
Pass OLD & NEW to a function
I've got 2 triggers. triggerA and triggerB that acts upon changes on tableA and tableB respectively. Inside both those triggers I want to use the same function that uses the OLD & NEW variables. I ...
- 754
8
votes
1
answer
34k
views
PostgreSQL: Auto update updated_at with custom column [duplicate]
Before, I would join multiple tables into one and return result, but in many tables I had same column names. That's why I decided to to prefix column names with table name. But that broke the trigger ...
- 183
2
votes
1
answer
2k
views
Call dblink from a trigger function
I have an environment where I have to move data from one database to another once a day. I Try to solve this by calling 'dblink' from a triggered function.
The following statement I can execute from ...
- 123
4
votes
1
answer
14k
views
Postgres function return table with extra column
Newbie to Postgres here..
I have a Postgres / plpgsql function that will return a table. I want to return everything from a query plus a logical value that I create and return along with it.
...
- 487
2
votes
1
answer
359
views
Postgresql trigger update whole table after delete
I have a table "measurements" which looks like this (with empty cells in the last col)
ser_nr | meas_ser | meas_value | last_meas
-------+----------+------------+-----------
A1 | 1 | 12 ...
- 115
-4
votes
1
answer
1k
views
PostgreSQL function incorrectly returns 0 rows [closed]
I'm learning how to create functions in Postgres. Below is a function I'm trying to get to return two numbers, but no matter what I try it always returns 0 rows.
Does anyone have any idea how I could ...
5
votes
2
answers
46k
views
RETURN value directly from INSERT with RETURNING clause
I have a function that ends with:
INSERT INTO configuration_dates (
cols...
) VALUES (
values...
) RETURNING id INTO ret_id;
RETURN ret_id;
And I would like to remove the ...
- 259
3
votes
1
answer
3k
views
Converting SQL SERVER Procedure to POSTGRESQL
I have procedure in SQL Server to print all players of given country.
I try to convert it into plpgsql, but I get errors.
SQL Server Procedure
CREATE PROCEDURE kraj_pilkarze @kraj varchar(30)
AS
...
- 107
1
vote
1
answer
1k
views
Trigger function - PostgreSQL 9.2
The idea:
1 - The customer can add any value into users.code column
2 - If the customer does not insert data into it, the trigger/funciton has to do the job
I am able to do that, using this SQL:
...
user83914
7
votes
2
answers
23k
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:
...
3
votes
1
answer
2k
views
Array of template type in PL/pgSQL function using %TYPE
I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE).
I want to update it so one of those arguments is ...
- 31
10
votes
1
answer
7k
views
PL/pgSQL issues when function used twice (caching problem?)
I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem.
I have this function:
CREATE FUNCTION sp_connect(mail character varying, passwd character ...
- 1,212
4
votes
1
answer
7k
views
Create a function from another function
Is it possible to create a new function from another plpgsql function? Something like this:
CREATE OR REPLACE FUNCTION func_test()
RETURNS VOID AS
$BODY$
BEGIN
CREATE OR REPLACE FUNCTION func_test2(...
- 143
0
votes
1
answer
99
views
How simplify/optimize a search of keywords in two tables at the same time?
I'm trying to model a function which will be used by a search engine (of cars for the example).
The last version of PostgreSQL will be installed on a Windows server (I don't know server's ...
- 101
3
votes
1
answer
6k
views
How to run ALTER TABLE on a list of tables sequentially in a function
I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
- 191