[v7.0.3] loop through a database and union all tables begiining with "j" and name is like j### (#=digit)

[Peter Paskoff]

how can i do that?

thx
peter

[Jeff Boes]

You probably want to write a function (PL/PgSQL would be best) which
returns a row of a type matching the typical union row. If all the
tables are identical, you could just pick one and have the function
return a row of that type. If they are different, and you are selecting
just a subset of fields from each table, then you may need to create a
"dummy table" of the appropriate type.

This is what I did in a similar circumstance; I needed to loop through
all tables matching "draft_[0-9]{5}" (that is, draft_00000 through
draft_99999) and return each row). However, I also needed to "tack on"
an extra column in each row so I could tell what table it came from.
Rather than use the table name, I used the integer corresponding to the
table name's suffix.

create table "all_drafts" ("editor_id" INTEGER) inherits ("draft_template");

create or replace function fn_all_drafts() returns setof all_drafts
as
'declare
t record;
r all_drafts%ROWTYPE;
draft_curs REFCURSOR;
editor_id INTEGER;
begin
for t in select relname from pg_class where relname ~
''^draft_[0-9]{5}$'' loop
editor_id := substring(t.relname from ''[0-9]{5}$'') :: integer;
open draft_curs for execute ''select * from '' ||
quote_ident(t.relname);
loop
fetch draft_curs into r;
exit when not found;
r.editor_id := editor_id;
return next r;
end loop;
close draft_curs;
end loop;
return;
end;' language 'plpgsql';

--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise

[Peter Paskoff]

Jeff Boes <jb...@qtm.net> wrote in message news:<2d4304d95a98ace7...@news.teranews.com>...

> You probably want to write a function (PL/PgSQL would be best)

thanks ... but ...

my server dosn´t support PL/PgSQL, only C, internal and SQL are avialable.

what should i do?

peter

[Peter Paskoff]

ok - language creates - was simple! sorry for my stupid question ...

... BUT :-)

this ist "my" function:

create function fn_all_jobcards() returns setof all_jobcards
as
'declare
t record;
r all_jobcards%ROWTYPE;
j_curs REFCURSOR;

editor_id INTEGER;
begin
for t in select relname from pg_class where relname ~

''^j_[0-9]{3}$'' loop
editor_id := substring(t.relname from ''[0-9]{3}$'') :: integer;
open j_curs for execute ''select * from '' ||
quote_ident(t.relname);
loop
fetch j_curs into r;

exit when not found;
r.editor_id := editor_id;
return next r;
end loop;

close j_curs;

end loop;
return;
end;' language 'plpgsql';

creatation is successful ...

but it returns:
# select fn_all_jobcards();

NOTICE: plpgsql: ERROR during compile of fn_all_jobcards near line 4
ERROR: parse error at or near "REFCURSOR"

whats wrong? plz help me!

greets
peter