pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC now needs updates for large objects
10+ messages / 4 participants
[nested] [flat]
* JDBC now needs updates for large objects
@ 2000-10-24 01:57 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2000-10-24 01:57 UTC (permalink / raw)
To: Peter Mount <petermount@maidstone.gov.uk>; +Cc: pgsql-interfaces; pgsql-hackers@postgreSQL.org
Peter,
As of current sources, large objects no longer occupy tables named
'xinvNNNN' nor indexes named 'xinxNNNN'. Therefore, it'd be appropriate
to remove the special tests that exclude tables/indices named that way
from the tests in DatabaseMetaData.java. I have not done this because
I'm not in a position to test changes to the JDBC driver; would you
please add it to your todo list?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: JDBC now needs updates for large objects
@ 2000-10-24 08:00 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Peter Mount @ 2000-10-24 08:00 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Peter Mount <petermount@maidstone.gov.uk>; +Cc: pgsql-interfaces; pgsql-hackers@postgresql.org
I was going to redo those queries this coming weekend anyhow (as thats when
I'll be getting some spare time next), as there are still some problems with
the existing ones.
Any other "minor" changes I should keep an eye out for?
Idea: As we have this type of query in more than one part of the source tree
(ie: psql, jdbc, probably odbc), should we have a section in the
documentation containing common queries, like: retrieving a list of tables,
views etc?
I haven't seen a definitive one in there, but it would be useful, and have
the other ones in the source be based on that one? Every time a change to
the system tables is made, unless everyone who maintains the code that's
dependent on it hears about it, the queries can quickly get out of sync.
Peter
--
Peter Mount
Enterprise Support Officer, Maidstone Borough Council
Email: petermount@maidstone.gov.uk
WWW: http://www.maidstone.gov.uk
All views expressed within this email are not the views of Maidstone Borough
Council
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, October 24, 2000 2:58 AM
To: Peter Mount
Cc: pgsql-interfaces@postgreSQL.org; pgsql-hackers@postgreSQL.org
Subject: JDBC now needs updates for large objects
Peter,
As of current sources, large objects no longer occupy tables named
'xinvNNNN' nor indexes named 'xinxNNNN'. Therefore, it'd be appropriate
to remove the special tests that exclude tables/indices named that way
from the tests in DatabaseMetaData.java. I have not done this because
I'm not in a position to test changes to the JDBC driver; would you
please add it to your todo list?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: [INTERFACES] RE: JDBC now needs updates for large objects
@ 2000-10-24 12:43 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Peter Mount @ 2000-10-24 12:43 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Peter Mount <petermount@maidstone.gov.uk>; +Cc: pgsql-interfaces; pgsql-hackers@postgresql.org
Yes, the joins were one of the reasons I was going to do it.
If no one starts a list by Saturday, then I'll start one when I go through
JDBC.
Peter
--
Peter Mount
Enterprise Support Officer, Maidstone Borough Council
Email: petermount@maidstone.gov.uk
WWW: http://www.maidstone.gov.uk
All views expressed within this email are not the views of Maidstone Borough
Council
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, October 24, 2000 1:44 PM
To: Peter Mount
Cc: pgsql-interfaces@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [INTERFACES] RE: JDBC now needs updates for large objects
Peter Mount <petermount@maidstone.gov.uk> writes:
> Idea: As we have this type of query in more than one part of the source
tree
> (ie: psql, jdbc, probably odbc), should we have a section in the
> documentation containing common queries, like: retrieving a list of
tables,
> views etc?
That's a good thought. It'd be a useful practice to review such
standard queries from time to time anyway. For example, now that
outer joins work, a lot of psql's backslash-command queries could
be simplified (don't need the UNION ALL WITH SELECT NULL hack).
Anyone have time to work up a list?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] RE: JDBC now needs updates for large objects
@ 2000-10-24 12:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2000-10-24 12:44 UTC (permalink / raw)
To: Peter Mount <petermount@maidstone.gov.uk>; +Cc: pgsql-interfaces; pgsql-hackers@postgresql.org
Peter Mount <petermount@maidstone.gov.uk> writes:
> Idea: As we have this type of query in more than one part of the source tree
> (ie: psql, jdbc, probably odbc), should we have a section in the
> documentation containing common queries, like: retrieving a list of tables,
> views etc?
That's a good thought. It'd be a useful practice to review such
standard queries from time to time anyway. For example, now that
outer joins work, a lot of psql's backslash-command queries could
be simplified (don't need the UNION ALL WITH SELECT NULL hack).
Anyone have time to work up a list?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: RE: JDBC now needs updates for large objects
@ 2000-10-25 08:28 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Dave Page @ 2000-10-25 08:28 UTC (permalink / raw)
To: pgsql-interfaces
> -----Original Message-----
> From: Peter Mount [mailto:petermount@maidstone.gov.uk]
> Sent: 24 October 2000 12:43
> To: 'Tom Lane'; Peter Mount
> Cc: pgsql-interfaces@postgreSQL.org; pgsql-hackers@postgreSQL.org
> Subject: RE: [INTERFACES] RE: JDBC now needs updates for
> large objects
>
>
> Yes, the joins were one of the reasons I was going to do it.
>
> If no one starts a list by Saturday, then I'll start one when
> I go through
> JDBC.
>
> Peter
I have done some work like this for the next release of pgAdmin (mainly
views to allow easy reporting on db objects) - if you want copies of what I
have let me know, it might be a starting point.
Dave.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: RE: JDBC now needs updates for large objects
@ 2000-10-25 15:15 Tony Simopoulos <karkalis@earthling.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Tony Simopoulos @ 2000-10-25 15:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
sorry,
i must have missed something. when did outer join's start working? is there a patch that i can build?
tonys.
> That's a good thought. It'd be a useful practice to review such
> standard queries from time to time anyway. For example, now that
> outer joins work, a lot of psql's backslash-command queries could
> be simplified (don't need the UNION ALL WITH SELECT NULL hack).
>
> Anyone have time to work up a list?
>
> regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: RE: JDBC now needs updates for large objects
@ 2000-10-25 15:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tony Simopoulos <karkalis@earthling.net>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2000-10-25 15:56 UTC (permalink / raw)
To: Tony Simopoulos <karkalis@earthling.net>; +Cc: pgsql-interfaces
"Tony Simopoulos" <karkalis@earthling.net> writes:
> i must have missed something. when did outer join's start working?
They're in current CVS (7.1-to-be). CVS tip is pretty unstable at
the moment with WAL stuff going on, but you could use it as a playpen
installation. Or wait for 7.1 beta, which should be out real soon now.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: RE: JDBC now needs updates for large objects
@ 2000-10-26 08:31 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Peter Mount @ 2000-10-26 08:31 UTC (permalink / raw)
To: 'Dave Page' <dpage@vale-housing.co.uk>; pgsql-interfaces
Yes please. It would be a good place to start from existing views.
Peter
--
Peter Mount
Enterprise Support Officer, Maidstone Borough Council
Email: petermount@maidstone.gov.uk
WWW: http://www.maidstone.gov.uk
All views expressed within this email are not the views of Maidstone Borough
Council
-----Original Message-----
From: Dave Page [mailto:dpage@vale-housing.co.uk]
Sent: Wednesday, October 25, 2000 9:29 AM
To: 'pgsql-interfaces@postgresql.org'
Subject: RE: [INTERFACES] RE: JDBC now needs updates for large objects
> -----Original Message-----
> From: Peter Mount [mailto:petermount@maidstone.gov.uk]
> Sent: 24 October 2000 12:43
> To: 'Tom Lane'; Peter Mount
> Cc: pgsql-interfaces@postgreSQL.org; pgsql-hackers@postgreSQL.org
> Subject: RE: [INTERFACES] RE: JDBC now needs updates for
> large objects
>
>
> Yes, the joins were one of the reasons I was going to do it.
>
> If no one starts a list by Saturday, then I'll start one when
> I go through
> JDBC.
>
> Peter
I have done some work like this for the next release of pgAdmin (mainly
views to allow easy reporting on db objects) - if you want copies of what I
have let me know, it might be a starting point.
Dave.
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: RE: JDBC now needs updates for large objects
@ 2000-10-31 12:45 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Dave Page @ 2000-10-31 12:45 UTC (permalink / raw)
To: 'Peter Mount' <petermount@maidstone.gov.uk>; pgsql-interfaces
> -----Original Message-----
> From: Peter Mount [mailto:petermount@maidstone.gov.uk]
> Sent: 26 October 2000 08:32
> To: 'Dave Page'; 'pgsql-interfaces@postgresql.org'
> Subject: RE: [INTERFACES] RE: JDBC now needs updates for
> large objects
>
>
> Yes please. It would be a good place to start from existing views.
>
> Peter
>
Sorry for the delay Peter, I've been in other offices hanging around for BT
engineers!
There are various views below *which work for me*. They are designed to
allow pgAdmin users to generate reports of the objects in their databases.
Note that I've had to use functions quite a lot to avoid outer joins and
unions, and that the comments column(s) in each view are retrieved from
either pg_description or pgadmin_desc depending on whether the oid of the
object is above 18655 (the last possible non-system oid from my
experiments).
Regards,
Dave.
Attachments:
sql.txt (text/plain, 9.9K) download
// Create various helper functions/tables
SELECT * INTO pgadmin_desc FROM pg_description WHERE objoid > 18655
CREATE TABLE pgadmin_seq_cache(sequence_oid oid, sequence_last_value int4, sequence_increment_by int4, sequence_max_value int4, sequence_min_value int4, sequence_cache_value int4, sequence_is_cycled text, sequence_timestamp timestamp DEFAULT now())
CREATE TABLE pgadmin_table_cache(table_oid oid, table_rows int4, table_timestamp timestamp DEFAULT now())
CREATE FUNCTION pgadmin_get_desc(oid) RETURNS text AS 'SELECT description FROM pgadmin_desc WHERE objoid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_pgdesc(oid) RETURNS text AS 'SELECT description FROM pg_description WHERE objoid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_col_def(oid, int4) RETURNS text AS 'SELECT adsrc FROM pg_attrdef WHERE adrelid = $1 AND adnum = $2' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_handler(oid) RETURNS text AS 'SELECT proname::text FROM pg_proc WHERE oid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_type(oid) RETURNS text AS 'SELECT typname::text FROM pg_type WHERE oid = $1' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_rows(oid) RETURNS pgadmin_table_cache AS 'SELECT DISTINCT ON(table_oid) * FROM pgadmin_table_cache WHERE table_oid = $1 ORDER BY table_oid, table_timestamp DESC' LANGUAGE 'sql'
CREATE FUNCTION pgadmin_get_sequence(oid) RETURNS pgadmin_seq_cache AS 'SELECT DISTINCT ON(sequence_oid) * FROM pgadmin_seq_cache WHERE sequence_oid = $1 ORDER BY sequence_oid, sequence_timestamp DESC' LANGUAGE 'sql'
// Datbases
CREATE VIEW pgadmin_databases AS SELECT
d.oid AS database_oid,
d.datname AS database_name,
d.datpath AS database_path,
pg_get_userbyid(d.datdba) AS database_owner,
CASE WHEN d.oid <= 18655 THEN pgadmin_get_pgdesc(d.oid) ELSE pgadmin_get_desc(d.oid) END AS database_comments
FROM
pg_database d
// Functions (only lists up to 16 args - ideas welcome!!)
CREATE VIEW pgadmin_functions AS SELECT
p.oid AS function_oid,
p.proname AS function_name,
pg_get_userbyid(p.proowner) AS function_owner,
rtrim(trim(
CASE WHEN (pgadmin_get_type(p.proargtypes[0]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[0]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[1]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[1]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[2]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[2]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[3]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[3]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[4]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[4]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[5]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[5]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[6]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[6]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[7]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[7]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[8]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[8]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[9]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[9]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[10]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[10]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[11]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[11]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[12]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[12]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[13]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[13]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[14]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[14]) || ', ' ELSE '' END ||
CASE WHEN (pgadmin_get_type(p.proargtypes[15]) NOTNULL) THEN pgadmin_get_type(p.proargtypes[15]) || ', ' ELSE '' END
), ',') AS function_arguments,
pgadmin_get_type(p.prorettype) AS function_returns,
p.prosrc AS function_source, l.lanname AS function_language,
CASE WHEN p.oid <= 18655 THEN pgadmin_get_pgdesc(p.oid) ELSE pgadmin_get_desc(p.oid) END AS function_comments
FROM
pg_proc p,
pg_language l
WHERE
p.prolang = l.oid
// Indexes
CREATE VIEW pgadmin_indexes AS SELECT
i.oid AS index_oid,
i.relname AS index_name,
c.relname AS index_table,
pg_get_userbyid(i.relowner) AS index_owner,
CASE WHEN x.indislossy = TRUE THEN 'Yes'::text ELSE 'No'::text END AS index_is_lossy,
CASE WHEN x.indisunique = TRUE THEN 'Yes'::text ELSE 'No'::text END AS index_is_unique,
CASE WHEN x.indisprimary = TRUE THEN 'Yes'::text ELSE 'No'::text END AS index_is_primary,
CASE WHEN i.oid <= 18655 THEN pgadmin_get_pgdesc(i.oid) ELSE pgadmin_get_desc(i.oid) END AS index_comments,
pg_get_indexdef(x.indexrelid) AS index_definition,
a.oid AS column_oid,
a.attname AS column_name,
a.attnum AS column_position,
t.typname As column_type,
CASE WHEN ((a.attlen = -1) AND ((a.atttypmod)::int4 = (-1)::int4)) THEN
(0)::int4
ELSE
CASE WHEN a.attlen = -1 THEN
CASE WHEN ((t.typname = 'bpchar') OR (t.typname = 'char') OR (t.typname = 'varchar')) THEN
(a.atttypmod -4)::int4
ELSE
(a.atttypmod)::int4
END
ELSE
(a.attlen)::int4
END
END AS column_length,
CASE WHEN a.oid <= 18655 THEN pgadmin_get_pgdesc(a.oid) ELSE pgadmin_get_desc(a.oid) END AS column_comments
FROM
pg_index x,
pg_attribute a,
pg_type t,
pg_class c,
pg_class i
WHERE
a.atttypid = t.oid AND
a.attrelid = i.oid AND
((c.oid = x.indrelid) AND (i.oid = x.indexrelid))
// Languages
CREATE VIEW pgadmin_languages AS SELECT
l.oid AS language_oid,
l.lanname AS language_name,
l.lancompiler AS language_compiler,
CASE WHEN l.lanpltrusted = TRUE THEN 'Yes'::text ELSE 'No'::text END AS language_is_trusted,
pgadmin_get_handler(lanplcallfoid) AS language_handler,
CASE WHEN l.oid <= 18655 THEN pgadmin_get_pgdesc(l.oid) ELSE pgadmin_get_desc(l.oid) END AS language_comments
FROM
pg_language l
// Sequences (this currently relies on pgAdmin scanning all sequences and bunging info into pgadmin_seq_cache)
CREATE VIEW pgadmin_sequences AS SELECT
c.oid AS sequence_oid,
c.relname AS sequence_name,
pg_get_userbyid(c.relowner) AS sequence_owner,
sequence_last_value(pgadmin_get_sequence(c.oid)) AS sequence_last_value,
sequence_increment_by(pgadmin_get_sequence(c.oid)) AS sequence_increment_by,
sequence_max_value(pgadmin_get_sequence(c.oid)) AS sequence_max_value,
sequence_min_value(pgadmin_get_sequence(c.oid)) AS sequence_min_value,
sequence_cache_value(pgadmin_get_sequence(c.oid)) AS sequence_cache_value,
sequence_is_cycled(pgadmin_get_sequence(c.oid)) AS sequence_is_cycled,
CASE WHEN c.oid <= 18655 THEN pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS sequence_comments
FROM
pg_class c
WHERE
c.relkind = 'S'
// Tables (this currently relies on pgAdmin creating a reliable record count for each table and putting it in pgadmin_table_cache)
CREATE VIEW pgadmin_tables AS SELECT
c.oid AS table_oid,
c.relname AS table_name,
pg_get_userbyid(c.relowner) AS table_owner,
c.relacl AS table_acl,
CASE WHEN c.relhasindex = TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_indexes,
CASE WHEN c.relhasrules = TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_rules,
CASE WHEN c.relisshared = TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_is_shared,
CASE WHEN c.relhaspkey = TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_primarykey,
CASE WHEN c.reltriggers > 0 THEN 'Yes'::text ELSE 'No'::text END AS table_has_triggers,
table_rows(pgadmin_get_rows(c.oid)) AS table_rows,
CASE WHEN c.oid <= 18655 THEN pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS table_comments,
a.oid AS column_oid,
a.attname AS column_name,
a.attnum AS column_position,
t.typname As column_type,
CASE WHEN ((a.attlen = -1) AND ((a.atttypmod)::int4 = (-1)::int4)) THEN
(0)::int4
ELSE
CASE WHEN a.attlen = -1 THEN
CASE WHEN ((t.typname = 'bpchar') OR (t.typname = 'char') OR (t.typname = 'varchar')) THEN
(a.atttypmod -4)::int4
ELSE
(a.atttypmod)::int4
END
ELSE
(a.attlen)::int4
END
END AS column_length,
CASE WHEN a.attnotnull = TRUE THEN 'Yes'::text ELSE 'No'::text END AS column_not_null,
CASE WHEN a.atthasdef = TRUE THEN 'Yes'::text ELSE 'No'::text END AS column_has_default,
CASE WHEN (pgadmin_get_col_def(c.oid, a.attnum) NOTNULL) THEN pgadmin_get_col_def(c.oid, a.attnum) ELSE '' END AS column_default,
CASE WHEN a.oid <= 18655 THEN pgadmin_get_pgdesc(a.oid) ELSE pgadmin_get_desc(a.oid) END AS column_comments
FROM
pg_attribute a,
pg_type t,
pg_class c
WHERE
a.atttypid = t.oid AND
a.attrelid = c.oid AND
(((c.relkind::char = 'r'::char) OR (c.relkind::char = 's'::char)) AND (NOT (EXISTS (SELECT pg_rewrite.rulename FROM pg_rewrite WHERE ((pg_rewrite.ev_class = c.oid) AND (pg_rewrite.ev_type::char = '1'::char))))))
// Triggers
CREATE VIEW pgadmin_triggers AS SELECT
t.oid AS trigger_oid,
t.tgname AS trigger_name,
c.relname AS trigger_table,
p.proname AS trigger_function,
t.tgtype AS trigger_type,
CASE WHEN t.oid <= 18655 THEN pgadmin_get_pgdesc(t.oid) ELSE pgadmin_get_desc(t.oid) END AS trigger_comments
FROM
pg_trigger t,
pg_class c,
pg_proc p
WHERE
c.oid = t.tgrelid AND
p.oid = tgfoid
// Views
CREATE VIEW pgadmin_views AS SELECT
c.oid AS view_oid,
c.relname AS view_name,
pg_get_userbyid(c.relowner) AS view_owner,
pg_get_viewdef(c.relname) AS view_definition,
CASE WHEN c.oid <= 18655 THEN pgadmin_get_pgdesc(c.oid) ELSE pgadmin_get_desc(c.oid) END AS view_comments
FROM
pg_class c
WHERE
(c.relhasrules AND (EXISTS (SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (r.ev_type::char = '1'::char)))))
* RE: RE: JDBC now needs updates for large objects
@ 2000-10-31 15:50 Peter Mount <petermount@maidstone.gov.uk>
0 siblings, 0 replies; 10+ messages in thread
From: Peter Mount @ 2000-10-31 15:50 UTC (permalink / raw)
To: 'Dave Page' <dpage@vale-housing.co.uk>; pgsql-interfaces; +Cc: Peter Mount (Home) <peter@retep.org.uk>
Prefixed with PM:
--
Peter Mount
Enterprise Support Officer, Maidstone Borough Council
Email: petermount@maidstone.gov.uk
WWW: http://www.maidstone.gov.uk
All views expressed within this email are not the views of Maidstone Borough
Council
-----Original Message-----
From: Dave Page [mailto:dpage@vale-housing.co.uk]
Sent: Tuesday, October 31, 2000 12:46 PM
To: 'Peter Mount'; 'pgsql-interfaces@postgresql.org'
Subject: RE: [INTERFACES] RE: JDBC now needs updates for large objects
> -----Original Message-----
> From: Peter Mount [mailto:petermount@maidstone.gov.uk]
> Sent: 26 October 2000 08:32
> To: 'Dave Page'; 'pgsql-interfaces@postgresql.org'
> Subject: RE: [INTERFACES] RE: JDBC now needs updates for
> large objects
>
>
> Yes please. It would be a good place to start from existing views.
>
> Peter
>
Sorry for the delay Peter, I've been in other offices hanging around for BT
engineers!
PM: I know the feeling. Anyhow, I've had problems with the storms yesterday
morning (had no power first thing, couldn't get into work etc), so when the
power came back on, I finally started on them.
PM: Started on the query to get details on the columns, and I'm seeing some
wierd results. The original query retrieved the oid of the column type, and
jdbc then queried for the type name as a separate query (yes, I'm going to
try to amalgamate these). Anyhow, on different tables, and it appears to be
random, but if it occurs for a table, it always occurs) the first query
returns the type name not the oid. I'm going to try to get a fresh cvs out
due to the make problems I was having, just incase it's something unusual
with the working 7.1 backend I have.
There are various views below *which work for me*. They are designed to
allow pgAdmin users to generate reports of the objects in their databases.
Note that I've had to use functions quite a lot to avoid outer joins and
unions, and that the comments column(s) in each view are retrieved from
either pg_description or pgadmin_desc depending on whether the oid of the
object is above 18655 (the last possible non-system oid from my
experiments).
PM: Yes, I currently implement this by issuing a separate query on
pg_description. Once I get the existing queries working I'm going to try to
use an outer join.
Regards,
Dave.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2000-10-31 15:50 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-24 01:57 JDBC now needs updates for large objects Tom Lane <tgl@sss.pgh.pa.us>
2000-10-24 08:00 RE: JDBC now needs updates for large objects Peter Mount <petermount@maidstone.gov.uk>
2000-10-24 12:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-25 15:15 ` Tony Simopoulos <karkalis@earthling.net>
2000-10-25 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-24 12:43 RE: [INTERFACES] RE: JDBC now needs updates for large objects Peter Mount <petermount@maidstone.gov.uk>
2000-10-25 08:28 RE: RE: JDBC now needs updates for large objects Dave Page <dpage@vale-housing.co.uk>
2000-10-26 08:31 RE: RE: JDBC now needs updates for large objects Peter Mount <petermount@maidstone.gov.uk>
2000-10-31 12:45 RE: RE: JDBC now needs updates for large objects Dave Page <dpage@vale-housing.co.uk>
2000-10-31 15:50 RE: RE: JDBC now needs updates for large objects Peter Mount <petermount@maidstone.gov.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox