All Questions
Tagged with dynamic-sql postgresql
501 questions
1
vote
4
answers
109
views
Switch ASC / DESC in ORDER BY with a CASE construct?
In PostgreSQL, I am trying to have SQL which orders by multiple columns. The first column will always be priority DESC NULLS LAST. The second one needs to be dynamic based on some conditions (for ease ...
1
vote
1
answer
97
views
How to declare variables and use them to UPDATE each row based on the preceding row(s)
I'm looping through table1:
create table table1(id,"date",quantity,"value")as values
(1,'2024-10-01',1,1)
,(2,'2024-10-02',1,1)
,(3,'2024-10-03',1,1)
,(4,'2024-10-04',1,1)
,(5,'...
- 137
0
votes
2
answers
120
views
Execute dynamic inserts in a procedure
I'm new to Postgres and trying to create code that inserts data from several tables into a single table.
All source tables have a name starting with "B3_HIST_", differing only in terms of ...
1
vote
1
answer
76
views
PostgreSQL Dynamic Query Execution with Table-Valued Variables
I am working with code inside of a PostgreSQL pgsql function. I have a merge statement that has multiple elements that are only known at runtime and are supplied to the function as parameters, which ...
- 419
1
vote
1
answer
53
views
How to create a view of a query generated by another dynamic (meta) query
How do I turn the query below into a temporary view? Everything I've tried returns the text of the generated query (second quoted code block). "Everything" includes...
Wrapping the 1st ...
- 3,044
1
vote
1
answer
226
views
Dynamic SQL vs. generic query with dynamic filters
I'm working on a SQL query where the filters can be dynamic, and I'm concerned about its efficiency. My current approach is as follows:
SELECT *
FROM table t
WHERE (:param1 IS NULL OR t.example = :...
- 317
4
votes
3
answers
297
views
Macros / Meta-programming in Postgres queries
In the case that I have the same example data as in this question and additionally declare the following two functions:
CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_price(...
- 1,492
0
votes
1
answer
25
views
Dynamic copy function
I have a function with a dynamic copy statement and when I try to SELECT function, I get the error "query has no destination for result data"
select loadTodaysData();
CREATE FUNCTION ...
1
vote
1
answer
90
views
How to club multiple select queries from one single table
I would like to query a table which has few columns (such as 4 below) from where i would like to create a snapshot table which can provide insight on the attribute count everyday
userId
attr1
attr2
...
- 47
1
vote
1
answer
106
views
Create columns from distinct values of a column
I have a large dataset with many different product. In a small scale it would look like this:
product
month
amount
AA
1
100
AA
1
150
AA
2
200
AA
2
120
BB
2
180
BB
2
220
CC
3
80
I want get the info in ...
- 25
0
votes
1
answer
286
views
Postgresql equivalent of SQL Server query
I am migrating database from SQL Server to Postgresql and I am currently stuck at converting this query. I have referred to the documentation but I don't seem to find any solution.
Here is the query (...
2
votes
1
answer
167
views
How to run the prepared statement with arguments dynamically in a function?
I created person table, then inserted 2 rows into it as shown below:
CREATE TABLE person (
id INT,
name VARCHAR(20),
age INT
);
INSERT INTO person (id, name, age)
VALUES (1, 'John', 27), (2, '...
0
votes
1
answer
117
views
dynamic sql to rename columns, which contain string in name
I am trying to rename all columns in a table, which contain the string survey_ by simply removing this string from the column name. I wish to do this with dynamic SQL. I've formulated the following ...
- 466
2
votes
1
answer
160
views
Explode hstore into new columns within row
I have the following table in PostgreSQL. The hstore extension is activated.
osm_id
hstore_column
172544
"kerb"=>"lowered", "highway"=>"crossing", "...
- 21
0
votes
1
answer
177
views
Trying to execute a dynamic CTE in PostgreSQL function
I'm trying to create a Postgresql function which will dynamically build the CTE and execute it to do the archive/purge.
Context: Records from main tables must be archived to their corresponding ...
- 3