By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,779 Members | 1,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,779 IT Pros & Developers. It's quick & easy.

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

P: n/a
how can i do that?

thx
peter
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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
Jul 19 '05 #2

P: n/a
Jeff Boes <jb***@qtm.net> wrote in message news:<2d******************************@news.terane ws.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
Jul 19 '05 #3

P: n/a
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
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.