Bind variables are not allowed in DDL statements. So following statements will cause errors: Example #1: DDL statement. Will cause ORA-01027: bind variables not allowed for data definition operations EXECUTE IMMEDIATE ‘CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT :def_val )’ USING 42; Example #2: DDL statement. Will cause ORA-00904: : invalid identifier EXECUTE IMMEDIATE ‘CREATE … Read more
You can use the provided C function crosstab_hash for this. The manual is not very clear in this respect. It’s mentioned at the end of the chapter on crosstab() with two parameters: You can create predefined functions to avoid having to write out the result column names and types in each query. See the examples … Read more
Basing my answer on a table of the form: CREATE TABLE tbl ( sl_no int , username text , designation text , salary int ); Each row results in a new column to return. With a dynamic return type like this, it’s hardly possible to make this completely dynamic with a single call to the … Read more
This is by design. The manual explains in the chapter Variable Substitution: Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the … Read more
You must pass in the parameters to sp_executesql. See MSDN for details. … WHERE CreatedBy = @p … EXECUTE sp_executesql @sql, N’@p UNIQUEIDENTIFIER’, @p = @p_CreatedBY
Sanitize function What you currently have can be simplified / sanitized to: CREATE OR REPLACE FUNCTION func_a (username text=””, databaseobject text=””) RETURNS ???? LANGUAGE plpgsql AS $func$ BEGIN RETURN QUERY EXECUTE format (‘SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)’ , CASE WHEN username=”*” THEN ‘view1’ ELSE ‘view3’ END , databaseobject); END … Read more
The problem with the former is that what you’re doing is USE ‘myDB’ rather than USE myDB. you’re passing a string; but USE is looking for an explicit reference. The latter example works for me. declare @sql varchar(20) select @sql=”USE myDb” EXEC sp_sqlexec @Sql — also works select @sql=”USE [myDb]” EXEC sp_sqlexec @Sql
I disagree with some of the advice in other answers. This can be done with PL/pgSQL and I think it is mostly far superior to assembling queries in a client application. It is faster and cleaner and the app only sends the bare minimum across the wire in requests. SQL statements are saved inside the … Read more
Stored Procedures Pro: Good for short, simple queries (aka OLTP–i.e. add, update, delete, view records) Pro: Keeps database logic separate from business logic Pro: Easy to troubleshoot Pro: Easy to maintain Pro: Less bits transferred over network (i.e. only the proc name and params) Pro: Compiled in database Pro: Better security (users don’t need direct … Read more
Basic query This query creates all necessary DDL statements: SELECT ‘DROP FUNCTION ‘ || oid::regprocedure FROM pg_proc WHERE proname=”my_function_name” — name without schema-qualification AND pg_function_is_visible(oid); — restrict to current search_path Output: DROP FUNCTION my_function_name(string text, form text, maxlen integer); DROP FUNCTION my_function_name(string text, form text); DROP FUNCTION my_function_name(string text); Execute the commands after checking plausibility. … Read more