Replace fields with new fields from another source

Hi Dean,

This might be possible with a View replacement import, as Yellowfin will try to match fields based on their name and data type for the column, if it can't be done with the UUID. If the names and data types match, it should replace the old fields with the new, despite them being 'different'.

Let me know if that works for you. Obviously replacing a view with another view carries some risk, so please take every precaution to ensure all data is backed up, etc.

Kind regards,

Chris

Thanks Chris

I'll test it out on our dev environment.

Fingers crossed!

Thanks

Dean

Hi Dean,

Hope you're well!

Just checking in with this one, did you get a chance to test?

Kind regards,

Chris

Hi Chris,

Not yet unfortunately

The change I want to make is part of a wider update to our tables and I'm still working out some issues on that side

It could be a week or 2 before I do so if you want, you can close the ticket and if needs be, I'll post back to open it back up

Thanks

Dean

Hi Dean,

No worries, I'll close this one off for now. Feel free to open it later!

Kind regards,

Chris

Hi Chris,

I managed to get this to work as per your suggestion. However, it took a bit of trial and error as the fields in question are used in quite a few calculated fields

I know this is something you don't normally recommend but is there a way to do this by editing the config DB? Perhaps whatever the application does when it replaces a field based on a name match

Even if you could point me to where I can find the field source information, that would be immensely helpful. I've tried looking around various tables but there is so many fields that I am having trouble finding it

Thanks

Dean

Hi Dean,

It's reportfield and reportfieldtemplate that contain field data. The UUID for a field would be in reportfieldtemplate. However you certainly run the risk of breaking your views/reports by modifying them in this way.

When I want to find things in my Postgres Yellowfin database, I use a global search function that takes a string argument and returns a table containing some information on where you can find it in the database. I'll include it here, perhaps you might find it useful.

CREATE OR REPLACE FUNCTION global_search(	
	search_term text,
	param_tables text[] default '{}',
	param_schemas text[] default '{public}',
	progress text default null -- 'tables','hits','all'
	)
	RETURNS table(schemaname text, tablename text, columnname text, rowctid tid)
	AS $$
	declare
	query text;
	hit boolean;
	begin
	FOR schemaname,tablename IN
	SELECT t.table_schema, t.table_name
	FROM information_schema.tables t
	JOIN information_schema.table_privileges p ON
	(t.table_name=p.table_name AND t.table_schema=p.table_schema
	AND p.privilege_type='SELECT')
	JOIN information_schema.schemata s ON
	(s.schema_name=t.table_schema)
	WHERE (t.table_name=ANY(param_tables) OR param_tables='{}')
	AND t.table_schema=ANY(param_schemas)
	AND t.table_type='BASE TABLE'
	LOOP
	IF (progress in ('tables','all')) THEN
	raise info '%', format('Searching globally in table: %I.%I',
	schemaname, tablename);
	END IF;
	query := format('SELECT ctid FROM %I.%I AS t WHERE strpos(cast(t.* as text), %L) > 0',
	schemaname,
	tablename,
	search_term);
	FOR rowctid IN EXECUTE query
	LOOP
	FOR columnname IN
	SELECT column_name
	FROM information_schema.columns
	WHERE table_name=tablename
	AND table_schema=schemaname
	LOOP
	query := format('SELECT true FROM %I.%I WHERE cast(%I as text)=%L AND ctid=%L',
	schemaname, tablename, columnname, search_term, rowctid);
	EXECUTE query INTO hit;
	IF hit THEN
	IF (progress in ('hits', 'all')) THEN
	raise info '%', format('Found in %I.%I.%I at ctid %s',
	schemaname, tablename, columnname, rowctid);
	END IF;
	RETURN NEXT;
	END IF;
	END LOOP; -- for columnname
	END LOOP; -- for rowctid
	END LOOP; -- for table
	END;
	$$ language plpgsql;

Kind regards,

Chris

Thanks Chris!

Yeah it's not something we're going to take lightly and we may not even go this route but I just wanted to see what would be involved doing it this way

Thanks

Dean

Hi Dean,

That's no worries. Do let me know if you give it a try.

Kind regards,

Chris

Hi Dean,

Hope everything is well with you.

Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.

Regards,

Chris