Stack Overflow
Stack Overflow

Questions tagged [dynamic-sql]

Dynamic SQL is a technique using SQL (Structured Query Language) whose main difference from traditional SQL is that Dynamic SQL allows to build SQL statements dynamically at runtime, which eases the automatic generation and execution of program statements.

2681 questions
22
votes
11 answers

ORA-01747: invalid user.table.column, table.column, or column specification

Get the above error when the execute immediate is called in a loop Update CustomersPriceGroups set 1AO00=:disc Where cuno=:cuno Parameters: disc=66 cuno=000974 Update CustomersPriceGroups set 1AP00=:disc Where cuno=:cuno Parameters: …
TonyP
  • 5,655
  • 13
  • 60
  • 94
22
votes
5 answers

PostgreSQL parameterized Order By / Limit in table function

I have a sql function that does a simple sql select statement: CREATE OR REPLACE FUNCTION getStuff(param character varying) RETURNS SETOF stuff AS $BODY$ select * from stuff where col = $1 $BODY$ LANGUAGE sql; For now I am invoking…
JoshuaBoshi
  • 1,266
  • 1
  • 14
  • 24
22
votes
4 answers

How can I drop all indexes of a table in Postgres?

I keep having this problem: I have like 20 indexes on a table that I need to drop in order to do testing. Dropping the table doesn't drop all of this metadata. There doesn't seem to be a wildcard drop index ix_table_* or any useful command. There…
user
  • 621
  • 1
  • 9
  • 21
20
votes
2 answers

Elegant way of handling PostgreSQL exceptions?

In PostgreSQL, I would like to create a safe-wrapping mechanism which returns empty result if an exception occurs. Consider the following: SELECT * FROM myschema.mytable; I could do the safe-wrapping in the client application: try { result =…
Tregoreg
  • 18,872
  • 15
  • 48
  • 69
20
votes
4 answers

T-SQL: How to use parameters in dynamic SQL?

I have the following dynamic query which is working fine without the WHERE clause, which is expecting UNIQUEIDENTIFIER. When I pass it in, I don't get a result. I tried CAST and CONVERT, but no result. I might be doing it wrong, can anybody…
Yaser Ahmed
  • 519
  • 2
  • 7
  • 15
19
votes
1 answer

Passing column names dynamically for a record variable in PostgreSQL

Using PostgreSQL, column values from a table for 1st record are stored in a record variable. for ex: let the variable be: recordvar recordvar.columnname gives the value of the column name specified. I will define the columname in a variable: var :=…
user2664380
  • 289
  • 1
  • 4
  • 8
19
votes
3 answers

How to search all text fields in a DB for some substring with T-SQL

I have a huge schema, with several hundreds of tables and several thousands of columns. I'd know that a specific IP address is stored in this database in several places, but I'm not sure what table(s) or column(s) it is stored in. Basically, I'm…
Jim
  • 6,753
  • 12
  • 44
  • 72
18
votes
3 answers

Dynamic SQL (EXECUTE) as condition for IF statement

I want to execute a dynamic SQL statement, with its returned value being the conditional for an IF statement: IF EXECUTE 'EXISTS (SELECT 1 FROM mytable)' THEN This generates the error ERROR: type "execute" does not exist. Is it possible to do…
Matt
  • 4,515
  • 5
  • 22
  • 29
17
votes
6 answers

Ad hoc queries vs stored procedures vs Dynamic SQL

Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons?
Kristaps
  • 187
  • 1
  • 1
  • 3
17
votes
1 answer

Function to loop through and select data from multiple tables

I'm new to Postgres and have a database with multiple tables of the same structure. I need to select data from each table that matches certain criteria. I could do this with a bunch of UNION queries, but the number of tables I need to search can…
user3813773
  • 233
  • 2
  • 3
  • 6
17
votes
1 answer

Return dynamic table with unknown columns from PL/pgSQL function

I need to create a function that checks on a given table if the infowindow field exists. If it exists the function must return select * from table but if it does not, it must return an additional id field: CREATE OR REPLACE FUNCTION…
Egidi
  • 1,736
  • 8
  • 43
  • 69
17
votes
1 answer

Return SETOF rows from PostgreSQL function

I have a situation where I want to return the join between two views. and that's a lot of columns. It was pretty easy in sql server. But in PostgreSQL when I do the join. I get the error "a column definition list is required". Is there any way I can…
user433023
  • 237
  • 1
  • 4
  • 13
17
votes
6 answers

Can I use MyBatis to generate Dynamic SQL without executing it?

I have some complex queries to build with a number of optional filters, for which MyBatis seems like an ideal candidate for generating dynamic SQL. However, I still want my query to execute in the same framework as the rest of the application (which…
Ray
  • 4,829
  • 4
  • 28
  • 55
16
votes
3 answers

Prepend table name to each column in a result set in SQL? (Postgres specifically)

How can I get the label of each column in a result set to prepend the name if its table? I want this to happen for queries on single tables as well as joins. Example: SELECT first_name, last_name FROM person; I want the results to be: |…
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
15
votes
1 answer

How to use variable as table name in plpgsql

I'm new to plpgsql. I'm trying to run a simple query in plpgsql using a variable as table name in plpgsql. But the variable is being interpreted as the table name instead of the value of the variable being interpreted as variable name. DECLARE …
Steven
  • 714
  • 2
  • 8
  • 21
1 2
3
…
99 100