16008

Dynamically generated CURSOR in Postgresql

Question:

I have got a cursor, it is pointing to a SELECT, but this select is generated dynamically. I want to assign the statement after the declarement. I have done an example working and another example NOT working. This is a simple example to print some data only. This is the table:

CREATE TABLE public.my_columns ( id serial NOT NULL, "name" varchar(30) NOT NULL, /* Keys */ CONSTRAINT my_columns_pkey PRIMARY KEY (id) ) WITH ( OIDS = FALSE ); CREATE INDEX my_columns_index01 ON public.my_columns ("name"); INSERT INTO public.my_columns ("name") VALUES ('name1'), ('name2'), ('name3'), ('name4'), ('name5'), ('name6');

This is the function(I have put the working code and the code not working):

CREATE OR REPLACE FUNCTION public.dynamic_table ( ) RETURNS text AS $$ DECLARE v_sql_dynamic varchar; --NOT WORKING: --db_c CURSOR IS (v_sql_dynamic::varchar); --WORKING: db_c CURSOR IS (SELECT id, name from public.my_columns); db_rec RECORD; BEGIN v_sql_dynamic := 'SELECT id, name from public.my_columns'; FOR db_rec IN db_c LOOP RAISE NOTICE 'NAME: %', db_rec.name; END LOOP; RETURN 'OK'; EXCEPTION WHEN others THEN RETURN 'Error: ' || SQLERRM::text || ' ' || SQLSTATE::text; END; $$ LANGUAGE plpgsql;

Any ideas?

Thank you.

Answer1:

Do you really need the explicit cursor? If you need iterate over dynamic SQL, then you can use FOR IN EXECUTE. It is loop over implicit (internal) cursor for dynamic SQL

FOR db_rec IN EXECUTE v_sql_dynamic LOOP .. END LOOP

Little bit more complex solution is described in <a href="http://www.postgresql.org/docs/9.4/static/plpgsql-cursors.html" rel="nofollow">documentation</a> - OPEN FOR EXECUTE:

do $$ declare r refcursor; rec record; begin open r for execute 'select * from pg_class'; fetch next from r into rec; while found loop raise notice '%', rec; fetch next from r into rec; end loop; close r; end $$;

With this kind of cursor, you cannot to use FOR IN

Recommend

  • UpdateException when using SQL Server Compact with Entity Framework
  • SyntaxError: Unexpected token ' in JSON at position 1
  • Cassandra eats memory
  • Get process output without blocking
  • Need to display iframe when link is clicked from menu
  • Getting p-values from leave-one-out in R
  • Vim syntax highlighting
  • In Ember.js, what's the difference between store save and store commit?
  • Why can't pass only 1 coulmn to glmnet when it is possible in glm function in R?
  • Matlab Generating a Matrix
  • Using C# Attributes and documentation
  • Error: java.util.Arrays$ArrayList cannot be cast to java.util.ArrayList
  • Storing data from SQL in array
  • if I want to find what's referencing an object in SQL Server, is searching syscomments comprehe
  • How to save dynamically created textboxes and their values
  • How to Model Ternary Relationship in CakePhp?
  • How do I remove all but some records based on a threshold?
  • Transactional Create with Validation in ServiceStack Redis Client
  • Meteor helpers not available in Angular template
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • PHP - How to update data to MySQL when click a radio button
  • Fill an image in a square container while keeping aspect ratio
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • Rearranging Cells in UITableView Bug & Saving Changes
  • bootstrap to use multiple ng-app
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Proper way to use connect-multiparty with express.js?
  • Understanding cpu registers
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs
  • Conditional In-Line CSS for IE and Others?