How to get the record column name from a dynamic query

Hi, i have this procedure where i send it some columns that i need  from some table
CREATE OR REPLACE FUNCTION DYNA_DATOS_COLUMNAS_F(PVH_COLS_NECESARIAS VARCHAR, PNH_fk_id_carga_cartera INTEGER) RETURNS integer AS $$
	DECLARE
	 VV_COLUMNAS_RETORNAR  varchar := '';

	  VV_QUERY_DINAMICO VARCHAR;
	  testRecord record;
	BEGIN
	VV_QUERY_DINAMICO:=' SELECT '|| PVH_COLS_DYNA ||' FROM as_detalle_carga WHERE fk_id_carga_cartera = ' || PNH_fk_id_carga_cartera || ' ;';
	--raise notice ' % ', VV_QUERY_DINAMICO;
	--printf("VV_QUERY_DINAMICO=%s\n",VV_QUERY_DINAMICO);
	--OPEN curs1 FOR EXECUTE VV_QUERY_DINAMICO;

	--FOR test IN EXECUTE VV_QUERY_DINAMICO LOOP
	--I NEED TO HANDLE THE DATA FOR EACH ROW
        --insert with the specific data would go here
	--	 	raise notice ' ELEMENT % ', testRecord ; 
--	END LOOP;
	return curs1;
		EXCEPTION WHEN OTHERS THEN
		raise notice '% %', SQLERRM, SQLSTATE;

			return -1;
			
	END;
	$$ LANGUAGE plpgsql;			

Open in new window


how can i get the testRecord.columnName  when the columns are dynamic?
Paul MonaSoftware developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I'm sure this will be different for the different databases that you are trying to use.

For Oracle, you couldn't use native dynamic SQL, you would have to use the DBMS_SQL package.  There are a few good examples in the documentation -> http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01113
0
Ray PaseurCommented:
My MySQL you might use the AS clause to render a predictable column name.
0
Paul MonaSoftware developerAuthor Commented:
the problem is that i dont know what columns i might have to pass to the query (column3,column5,column1, etc),  so how can i call them in the record variable (record.column3, record.column5, record.column1, record.? ans so on )
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Aaron TomoskySD-WAN SimplifiedCommented:
If you know the order you can reference columns as a number for order by, never tried it for anything else.

Select * from tablename order by 1,3

You could insert the results into a temp table, then you would know the column names for that.
0
Ray PaseurCommented:
Are you using a high-level scripting language like PHP?  If so you can use variable names to create the query.

You can determine the names of the existing columns in the table with SHOW CREATE TABLE.
0
johnsoneSenior Oracle DBACommented:
Can you confirm what database you are using for this?

When using DBMS_SQL in Oracle, you can call DESCRIBE_COLUMNS to get the names and data types of all the columns in the query.  This is shown in the examples that I linked to.
0
MikeOM_DBACommented:
Obviously, if you  are already providing the names of the columns in "PVH_COLS_NECESARIAS",
Those are the columns returned by the query!
DUH!
Need the data type? use those name to query {ALL|DBA|USER}_TAB_COLUMNS
0
Paul MonaSoftware developerAuthor Commented:
1. Im using postgres.
2. im using $$ LANGUAGE plpgsql.
3. im dont need the data type.
2. I have the column names the main problem is that i dont know how to recall them on the record variable , i try record.1 , record.{1} , record.$1  when i try to iterate trough the dynamic query

FOR test IN EXECUTE VV_DYNAMIC_QUERY LOOP
		 	raise notice ' ELEMENT % ', test.1 ????; 
	END LOOP;

Open in new window


thanks for all the replies :)!!!
0
earth man2Commented:
Most straightforward way would be to use returned cursor.

FETCH ALL FROM the_cursor_name;

All the column names and row data are contained within the cursor structure so should be extractable from a system table.
Ps to use cursors you need to be in a transaction so BEGIN ... COMMIT; needs to surround the sql.
0
earth man2Commented:
Need to know how you are going to use the data to get best solution.
0
Paul MonaSoftware developerAuthor Commented:
Need to know how you are going to use the data to get best solution.
I am gonna insert it on a diferent table this is why im using this method
1. upload file to a generic table
2. get data from certain columns (depending on configs)
3. insert the data on the table that it suppost to go to.

About the cursor, how can i recall the data on the cursor (dataFromcolumn:=cursor.column1) when i dont know the name of the column
0
earth man2Commented:
you don't need to know the name of the columns only the number of columns and type.
eg just use text as most column types can be easily transmuted to text.

OPEN unbound_cursorvar FOR EXECUTE ' SELECT $1 FROM as_detalle_carga WHERE fk_id_carga_cartera = $2' USING PVH_COLS_DYNA, PNH_fk_id_carga_cartera;

FETCH unbound_cursorvar INTO rowvar;
or
FETCH unbound_cursorvar INTO foo, bar, baz;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.