All Questions
Tagged with plpgsql postgresql-performance
9 questions
0
votes
0
answers
81
views
Deduplicate while updating self-references in the table, and foreign references in another (PostgreSQL)
I've read a few posts here that I've been able to find about this, and they usually involve a comment about how the poster has inherited a mess, and they're trying to clean it up. Unfortunately, I am ...
- 67
1
vote
0
answers
3k
views
Postgres - updating millions of rows
I am trying to update a column in a table with around 30 millions of rows without downtime.
To do that, I am not dropping indexes and constraints because I want to keep the table working normally ...
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
-1
votes
1
answer
100
views
Optimize postgres function
I have following function which I want to use to update status and distance columns.
CREATE OR REPLACE FUNCTION public.check_deviation_table(
)
RETURNS void
LANGUAGE 'plpgsql'
COST ...
- 113
0
votes
1
answer
3k
views
Using function is much slower than running query
I am trying to write a plpgsql function that runs a query where the WHERE part is variable using EXECUTE, like this :
CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$
DECLARE
q ...
- 73
1
vote
0
answers
2k
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 ...
- 81
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
4
votes
2
answers
9k
views
Assign values with the := operator
When I assign a variable with
result := title || '', by '' || author;
it is taking more time (approx. 15 sec) to run the function.
However, when I assign the variable with
result = title || '', by '...
- 183