All Questions
16 questions
1
vote
1
answer
111
views
Conditionally execute create command in SQL
I want to create a SQL function that can perform the following task - It should check whether a schema exists and if it doesn't, the function should create one.
Here is an example of such a function -
...
- 149
0
votes
2
answers
2k
views
Stored Procedure to Update Table with a Variable Column Name
I am trying the write a stored procedure in plpgsql to simply update an integer value in a table. I have a table (called conditions) with four columns of type integer called structure, missing, ...
- 3
0
votes
1
answer
1k
views
How can I use if exists with execute?
I am unable to create this stored procedure due to syntax error. Is here any one can solve this syntax issue?
DROP PROCEDURE REMOTE_DB_CONNECTION_REMOVER;
CREATE PROCEDURE REMOTE_DB_CONNECTION_REMOVER(...
0
votes
1
answer
1k
views
How to execute a SQL string returned from a stored procedure in Postgresql?
I have a simple stored procedure below which is returning a SQL string:
CREATE OR REPLACE PROCEDURE dbo.select_users_test(sqlstring inout text)
LANGUAGE plpgsql
AS
$$
BEGIN
sqlstring = 'select ...
- 1,711
0
votes
2
answers
106
views
Array error passing dynamic number of parameters to function
I'm trying to create a function to receive the name of the table in my schema already created and a several name of columns within this table (dynamic number of columns) and return a table with all ...
1
vote
3
answers
1k
views
Any way to get a more meaningful error message in postgres?
Let's have some tea ...
CREATE OR REPLACE FUNCTION allRelevantTeas() RETURNS TABLE(tea_id INTEGER) AS $function$
DECLARE
result REFCURSOR;
stmt TEXT;
countries_with_tea TEXT[] := array['...
- 8,239
0
votes
1
answer
310
views
Postgres Trigger Function Dynamic Column Concat TG_TABLE_NAME
I want to know if it's possible to do something like this on Postgres 11 in a trigger function.
CREATE OR REPLACE FUNCTION "searchs"."handle_content" ( )
RETURNS trigger
AS $BODY$
BEGIN
UPDATE ...
- 134
0
votes
1
answer
241
views
Postgresql - how to call procedure, name which is into variable
how can I call procedure from variable
var proc_name := 'proc1()'
call proc_name;
0
votes
1
answer
2k
views
What are my options for using dynamic sort field/order in postgresql?
I have found that i can use dynamic sql to plug in parameters for the sort field and the sort direction. I have also found that to plug those parameters into the dynamic sql one can use "USING param1, ...
- 2,509
9
votes
2
answers
9k
views
Postgresql - how to run a query on multiple tables with same schema
I have a postgres database that has several tables (a few hundreds). A subset Foo of the tables in the database have the same schema.
Ideally, I would like to create a stored procedure which can run ...
- 68.6k
2
votes
1
answer
677
views
Unexpected behavior of dynamic SQL using regclass
I have some weird behavior using EXECUTE with regclass and I'm trying to debug this and need some help.
Basically, I'm trying to run these SQL statements within a function:
ALTER TABLE mytable_bak ...
- 1,505
1
vote
2
answers
286
views
How to use variable as table name?
BEGIN
_table_name := 'mytable';
CREATE TEMPORARY TABLE _table_name (
id integer NOT NULL,
name character varying,
active boolean
) ON COMMIT DROP';
-- logic here
RETURN ...
- 3,873
2
votes
1
answer
16k
views
How to execute a string result of a stored procedure in postgres
I have created the following stored procedure, which basically receives a name of table, and a prefix. The function then finds all columns that share this prefix and returns as an output a 'select' ...
- 743
3
votes
3
answers
5k
views
Check if table is empty in runtime
I am trying to write a script which drops some obsolete tables in Postgres database. I want to be sure the tables are empty before dropping them. I also want the script could be kept in our migration ...
- 363
0
votes
2
answers
17k
views
Syntax error at or near "FOR" while using "execute format" in a function
This function compiled successfully:
CREATE OR REPLACE FUNCTION FieldValidations1(tbl_name varchar(35),col_name varchar(25), error_flag varchar(3))
RETURNS void AS $$
declare
cust_rec RECORD;
...
- 847