Linked Questions

73
votes
10answers
10k views

What are the arguments against or for putting application logic in the database layer?

NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting ...
65
votes
5answers
148k views

How to turn json array into postgres array?

I have a column data that holds a json document roughly like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string (foo, bar). ...
30
votes
1answer
16k 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?
15
votes
1answer
11k views

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN ...
15
votes
2answers
2k views

Simultaneous calls to the same function: how are deadlocks occurring?

My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not exists'&...
4
votes
2answers
2k views

In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?

I understand the distinction between: Scalar functions Set-Returning-Functions (SRF)s Internal functions Window functions Aggregate functions of all sorts User-Implemented functions (which in ...
5
votes
4answers
4k views

Does it make sense to have CASE .. END in an ORDER BY?

Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice? It is common to use parameters ...
3
votes
1answer
16k views

Equivalent functions in MySQL that exist in SQL Server

In an EXIT HANDLER block, what are the MySQL equivalents of OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) ERROR_MESSAGE() Edit What I want to do. In SQL Server I'd have CREATE PROC .....
2
votes
1answer
4k views

When to use stored procedure / user-defined function? [closed]

I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?
3
votes
3answers
1k views

How do I round time to the upper multiple of an arbitrary time interval?

Example: If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00. If the current time is 2018-05-17 22:45:30 and the ...
4
votes
1answer
2k 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
vote
1answer
4k views

Wildcard search using parameters in function with dynamic SQL

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter ...
5
votes
2answers
597 views

Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

I have a really busy function that I need to optimize the best way I can. This function is just a nested select statement that is requested several times a sec by a legacy application. The indexes ...
2
votes
1answer
680 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
5
votes
2answers
150 views

Joining a function and a view

I have a function get_sa001 and a view Axis_RefCustomer. When I execute my function get_sa001 on it's own for a certain period let's say 2016 - 2017, the execution time is about ~6 seconds. SELECT d....

15 30 50 per page