sql - Add schema name to dynamic Postgres query -

-

my database using postgres schemas provide separated, multi-tenant environment users. every schema has copy of same tables.

i have 1 particular query need join across schemas, return list of records (in case, children). have working via dynamic sql query, shown below. however, want add in column each result specifies name of schema row came from.

current dynamic query

(schema's like: operator_schema_my-great-company)

create or replace function all_children_dynamic() returns setof children $$ declare   schema record; begin   schema in execute     format(       'select schema_name information_schema.schemata left(schema_name, 16) = %l',       'operator_schema_'     )   loop     return query execute       format('select * %i.children', schema.schema_name);   end loop; end; $$ language plpgsql;  --------  -- usage: select "id", "name" all_children_dynamic();

this returns like:

------------- | id | name | | 1  | bob  | | 2  | joe  | -------------

whereas 'd return like: 

------------------------------- | id | name | schema_name     | | 1  | bob  | darcy's-store   | | 2  | joe  | bob's-4th-store | -------------------------------

it should noted schema names user defined, , can have quotes in them.

how can add in relevant schema name each child?

i have tried few variations of following:

loop    return query execute      format('select %s schema_name, * %1$i.children', schema.schema_name); end loop;

but i'm having issues formatting etc. there's quote_x functionality should using here.

i'm not knowledgable on postgres (and databases in general) patience appreciated!

updates

the following exact errors getting few variations.

input: format('select %s schema_name, * %1$i.children', schema.schema_name);  error:  column "operator_schema_don" not exist line 1: select operator_schema_don-t-display-data schema_name, * ... query:  select operator_schema_don-t-display-data schema_name, * "operator_schema_don-t-display-data".children  input: format('select %s schema_name, * %i.children', quote_literal(schema.schema_name), schema.schema_name);  error:  structure of query not match function result type detail:  returned type unknown not match expected type uuid in column 1.

update 2

i'm getting closer, not quite there yet.

create or replace function all_children_dynamic() returns table (id uuid, schema_name varchar) $$ declare   schema record; begin   schema in execute     format(       'select schema_name information_schema.schemata left(schema_name, 16) = %l',       'operator_schema_'     )   loop     return query execute       format('select id, %l schema_name %i.children', quote_literal(schema.schema_name), schema.schema_name);   end loop; end; $$ language plpgsql;  error:  structure of query not match function result type detail:  returned type unknown not match expected type character varying in column 2. context:  pl/pgsql function all_children_dynamic() line 11 @ return query

why the return type coming unknown? expected inserting string , returning type.

since adding new column (in case schema name) query, function result not setof children. table type needs returned additional column included in it. syntax can seen here .

something this..

create or replace function all_children_dynamic() returns  table(col1 col1type,col2 col2type,...., schema_name varchar) $$

the second error due improper casting postgres since version 9 has been real specific return data types. example. if returning varchar(8) in function return type, have return varchar of same length. hence casting required.


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more