Re: [HACKERS] Generalized edit function?
Robert Haas gmail.com> writes: > > On Sat, Feb 26, 2011 at 4:19 PM, Josh Berkus agliodbs.com> wrote: > > Anyway, if it's ASCII-only, that's a guaranteed way to make sure it > > isn't taken seriously. > > Pre-9.1 levenshtein is ASCII-only, and I think some of the other stuff > in contrib/fuzzystrmatch still is. I am only looking at 9.0.3 for levenshtein, so I don't have any thoughts yet on multi-byteness so far. I will have to figure out the multibyte character work once I get the basic algorithm working -- any thoughts on that? Any pitfalls in porting? > So I have some sympathy with the OP's desire not to burden himself > with the non-ASCII case if he doesn't need it for his application, > but > I also agree with your point that we probably wouldn't accept code > into contrib that doesn't. Good to know. I will try to avoid backing myself into an ascii corner. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Generalized edit function?
Hi hackers, I am interested in extending Postgres with a "generalized edit function" like SAS's "compged"[1], which is basically levenshtein distance with transposes (ab <-> ba) and LOTS of different weights for certain ops (like insert a blank versus delete from the end versus insert a regular character). Compged seems to work really well for us when trying to match addresses (MUCH better than pure levenshtein), and it would be a great tool for data miners. I have a number of questions: 1. Does anybody else care? I would love to see this in contrib, but if the chances are slim, then I would like to know that too. 2. Has anybody else done something like this and can give ideas or source? It seems to me that the code will have to be a mess of pointers and indexes, but if there is some theory that simplifies it I haven't heard about it. (Levenshtein without transposes is theoretically clean, but I think the fact that we have transposes means we look ahead 2 chars and lose all the nice dynamic programming stuff.) 3. I will probably implement this for ascii characters -- if anyone has any thoughts on other encodings, please share. Thanks for everyone's time. I will try to implement a command line version and put that on pastebin for people to look at while I port it to the postgres environment. [1] (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002206133.htm) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PHP 4.0.4pl1 / Beta 5
FWIW, I emailed Thies about the pg_connect problems, and whis is what he
responded with (yesterday would be Feb 13):
i've commited a fix for this to PHP 4 CVS yesterday.
if you don't want to live on the "bleeding edge" (use PHP
from CVS) just replace the php_pgsql_set_default_link
function in pgsql.c against this one and you're all-set!
regards,
tc
static void php_pgsql_set_default_link(int id)
{
PGLS_FETCH();
if ((PGG(default_link) != -1) && (PGG(default_link) != id)) {
zend_list_delete(PGG(default_link));
}
if (PGG(default_link) != id) {
PGG(default_link) = id;
zend_list_addref(id);
}
}
-
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Sun, 18 Feb 2001, Bruce Momjian wrote:
> [ Charset ISO-8859-1 unsupported, converting... ]
> > I sure hope it gets more attention than some of the other PHP PostgreSQL
> > bugs.. I don't mean to trash anyone here but the pg_connect problem has been
> > around since 4.0.1 and has yet to be addressed. One of our programmers is
> > taking a look at that one but he's not been able to fix it yet.
>
> I have worked with Thies on getting persistent connections to work
> better. If there are any PostgreSQL problems with PHP, I recommend
> sending something to him as he is focused on PostgreSQL recently.
>
> --
> Bruce Momjian| http://candle.pha.pa.us
> [EMAIL PROTECTED] | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
>
[HACKERS] Re: [GENERAL] Re: grant privileges to a database
This is the closest thing to what you want: GRANT ALL ON table TO user; (see http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Wed, 31 Jan 2001, Martin A. Marques wrote: > El Mar 30 Ene 2001 10:08, Martin A. Marques escribió: > > Is there a way to grant a user with all privileges on a database? Something > > like Informixs GRANT dba? > > OK, I see that no one responded (except one person how made a personal > responce), so I'll add a bit to it. > I seem to be aware that Postgres doesn't have a function to grant total > access to a database, so is it posible to have this added to the todo list > for further versions? Or is it posible to build a user-function that will let > me do this: > > postgres@ultra3:~ > psql horde > > horde=# CREATE DATABASE test; > CREATE DATABASE > horde=# GRANT dba TO martin; -- here is the grant issue. > GRANT > horde=# \c test martin; > You are now connected to database test as user martin. > test=# CREATE TABLE (aid SERIAL, texto TEXT); > CREATE TABLE > test=# > > Saludos... ;-) > > > -- > System Administration: It's a dirty job, > but someone told I had to do it. > - > Martín Marquésemail: [EMAIL PROTECTED] > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > Administrador de sistemas en math.unl.edu.ar > - >
Re: AW: [HACKERS] Three types of functions, ala function redux.
Acutally, a function can use an index scan *if* it is marked as cacheable: (the "test" table has 1 field, col (type is int4), which is populated with numbers 1 thru 5000) testdb=# create function func_test_cache (int4) returns int4 as ' testdb'# select $1; testdb'# ' LANGUAGE 'sql' with (iscachable); CREATE testdb=# create function func_test (int4) returns int4 as ' testdb'# select $1; testdb'# ' LANGUAGE 'sql'; CREATE testdb=# vacuum analyze; VACUUM testdb=# explain select * from test where col = func_test_cache(1); NOTICE: QUERY PLAN: Index Scan using idxtest on test (cost=0.00..2.01 rows=1 width=4) EXPLAIN testdb=# explain select * from test where col = func_test(1); NOTICE: QUERY PLAN: Seq Scan on test (cost=0.00..100.00 rows=1 width=4) EXPLAIN Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Thu, 21 Dec 2000, mlw wrote: > Zeugswetter Andreas SB wrote: > > > > > select * from table where col = function() ; > > > > > (2) "function()" returns a number of values that are independent of the > > > query. Postgres should be able to optimize this to be: "select * from > > > table where col in (val1, val2, val3, ..valn)." I guess Postgres can > > > loop until done, using the isDone flag? > > > > I think the above needs a different sql statement to begin with. > > The "= function()" clearly states that function is only allowed to return one row. > > > > The following syntax currently works, and is imho sufficient: > > select * from table where col in (select function()); > > Both syntaxes work, but always force a table scan. If you have an index > on 'col' it will not be used. If your table has millions of records, > this takes time. > > -- > http://www.mohawksoft.com >
RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. (fwd)
Here's the query that, given the primary key table, lists all foreign keys, their tables, the RI type, and defereability. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio SELECT pg_trigger.tgargs, pg_trigger.tgnargs, pg_trigger.tgdeferrable, pg_trigger.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pg_class, pg_class pg_class_1, pg_class pg_class_2, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_trigger pg_trigger_2 WHERE pg_trigger.tgconstrrelid = pg_class.oid AND pg_trigger.tgrelid = pg_class_1.oid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pg_class_1.oid AND pg_trigger_2.tgconstrrelid = pg_class_2.oid AND pg_trigger_2.tgfoid = pg_proc.oid AND pg_class_2.oid = pg_trigger.tgrelid AND ((pg_class.relname='<>') AND (pg_proc.proname Like '%upd') AND (pg_proc_1.proname Like '%del') AND (pg_trigger_1.tgrelid=pg_trigger.tgconstrrelid) AND (pg_trigger_2.tgrelid = pg_trigger.tgconstrrelid)) On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote: > Thanks mike - chances are it will be committed to phpPgAdmin by the end of > the week! > > BTW, you may wish to make sure that your email as cc'd to the hacker's list > as well. > > Regards, > > Chris > > -- > Christopher Kings-Lynne > Family Health Network (ACN 089 639 243) > > > -Original Message- > > From: Michael Fork [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, December 05, 2000 12:25 PM > > To: Christopher Kings-Lynne > > Subject: RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. > > > > > > There ya go, I figured it out :) Given the name a table, this query will > > return all foreign keys in that table, the table the primary key is in, > > the name of the primary key, if the are deferrable, if the are initially > > deffered, and the action to be performed (RESTRICT, SET NULL, etc.). To > > get the foreign keys and primary keys and tables, you must parse the > > null-terminated pg.tgargs. > > > > When I get the equivalent query working for primary keys I will send it > > your way -- or if you beat me to it, send it my way (I am working on some > > missing functionality from the ODBC driver) > > > > Michael Fork - CCNA - MCP - A+ > > Network Support - Toledo Internet Access - Toledo Ohio > > > > SELECT pt.tgargs, > > pt.tgnargs, > > pt.tgdeferrable, > > pt.tginitdeferred, > > pg_proc.proname, > > pg_proc_1.proname > > FROM pg_class pc, > > pg_proc pg_proc, > > pg_proc pg_proc_1, > > pg_trigger pg_trigger, > > pg_trigger pg_trigger_1, > > pg_proc pp, > > pg_trigger pt > > WHERE pt.tgrelid = pc.oid > > AND pp.oid = pt.tgfoid > > AND pg_trigger.tgconstrrelid = pc.oid > > AND pg_proc.oid = pg_trigger.tgfoid > > AND pg_trigger_1.tgfoid = pg_proc_1.oid > > AND pg_trigger_1.tgconstrrelid = pc.oid > > AND ((pc.relname='<>') > > AND (pp.proname LIKE '%%ins') > > AND (pg_proc.proname LIKE '%%upd') > > AND (pg_proc_1.proname LIKE '%%del') > > AND (pg_trigger.tgrelid=pt.tgconstrrelid) > > AND (pg_trigger_1.tgrelid = pt.tgconstrrelid)) > > > > > > On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote: > > > > > Hi Michael, > > > > > > I am on the phpPgAdmin development team, and I have been > > wanting to add this > > > functionality to phpPgAdmin. I will start working with your > > query as soon > > > as possible, and I will use phpPgAdmin as a testbed for the > > functionality. > > > > > > I really appreciate having your query as a working basis, because it's > > > really hard trying to figure out the system tables! > > > > > > Chris > > > > > > > -Original Message- > > > > From: [EMAIL PROTECTED] > > > > [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Fork > > > > Sent: Sunday, December 03, 2000 12:23 PM > > > > To: [EMAIL PROTECTED] > > > > Subject: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. > > > > > > > > > > > > Given the name of a table, I need to find all foreign keys in > > that table > > > > and the table/column that they refer to, along with the action to be > > > > performed on update/delete. The following query works, but only when > > > > there is 1 foreign key in the table, when there is more than > > 2 it grows > > > > exponentially -- which means I am missi
RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. (fwd)
There ya go, I figured it out :) Given the name a table, this query will return all foreign keys in that table, the table the primary key is in, the name of the primary key, if the are deferrable, if the are initially deffered, and the action to be performed (RESTRICT, SET NULL, etc.). To get the foreign keys and primary keys and tables, you must parse the null-terminated pg.tgargs. When I get the equivalent query working for primary keys I will send it your way -- or if you beat me to it, send it my way (I am working on some missing functionality from the ODBC driver) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pc, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND pg_trigger.tgconstrrelid = pc.oid AND pg_proc.oid = pg_trigger.tgfoid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pc.oid AND ((pc.relname='<>') AND (pp.proname LIKE '%%ins') AND (pg_proc.proname LIKE '%%upd') AND (pg_proc_1.proname LIKE '%%del') AND (pg_trigger.tgrelid=pt.tgconstrrelid) AND (pg_trigger_1.tgrelid = pt.tgconstrrelid)) On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote: > Hi Michael, > > I am on the phpPgAdmin development team, and I have been wanting to add this > functionality to phpPgAdmin. I will start working with your query as soon > as possible, and I will use phpPgAdmin as a testbed for the functionality. > > I really appreciate having your query as a working basis, because it's > really hard trying to figure out the system tables! > > Chris > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Fork > > Sent: Sunday, December 03, 2000 12:23 PM > > To: [EMAIL PROTECTED] > > Subject: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc. > > > > > > Given the name of a table, I need to find all foreign keys in that table > > and the table/column that they refer to, along with the action to be > > performed on update/delete. The following query works, but only when > > there is 1 foreign key in the table, when there is more than 2 it grows > > exponentially -- which means I am missing a join. However, given my > > limitied knowledge about the layouts of the postgres system tables, and > > the pg_trigger not being documented on the web site, I have been unable to > > get the correct query. Is this possible, and if so, what join(s) am I > > missing? > > > > SELECT pt.tgargs, > > pt.tgnargs, > > pt.tgdeferrable, > > pt.tginitdeferred, > > pg_proc.proname, > > pg_proc_1.proname > > FROM pg_class pc, > > pg_proc pg_proc, > > pg_proc pg_proc_1, > > pg_trigger pg_trigger, > > pg_trigger pg_trigger_1, > > pg_proc pp, > > pg_trigger pt > > WHERE pt.tgrelid = pc.oid > > AND pp.oid = pt.tgfoid > > AND pg_trigger.tgconstrrelid = pc.oid > > AND pg_proc.oid = pg_trigger.tgfoid > > AND pg_trigger_1.tgfoid = pg_proc_1.oid > > AND pg_trigger_1.tgconstrrelid = pc.oid > > AND ((pc.relname='tblmidterm') > > AND (pp.proname LIKE '%ins') > > AND (pg_proc.proname LIKE '%upd') > > AND (pg_proc_1.proname LIKE '%del')) > > > > Michael Fork - CCNA - MCP - A+ > > Network Support - Toledo Internet Access - Toledo Ohio > > >
Re: [HACKERS] beta testing version
Judging by the information below, taken *directly* from PostgreSQL, Inc. website, it appears that they will be releasing all code into the main source code branch -- with the exception of "Advanced Replication and Distributed Information capabilities" (to which capabilities they are referring is not made clear) which may remain proprietary for up to 24 months "in order to assist us in recovering development costs and continue to provide funding for our other Open Source contributions." I have interpreted this to mean that basic replication (server -> server, server -> client, possibly more) will be available shortly for Postgres (with the release of 7.1?) and that those more advanced features will follow behind. This is one of the last features that was missing from Postgres (along with recordset returning functions and clusters, among others) that was holding it back from the enterprise market -- and I do not blame PostgreSQL, Inc. one bit for withholding some of the more advanced features to recoup their development costs -- it was *their time* and *their money* they spent developing the *product* and it must be recoup'ed for projects like this to make sense in the future (who knows, maybe next they will implement RS returning SP's or clusters, projects that are funded with their profit off the advanced replication and distributed information capabilities that they *may* withhold -- would people still be whining then?) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio (http://www.pgsql.com/press/PR_5.html) "At the moment we are limiting our test groups to our existing Platinum Partners and those clients whose requirements include these features." advises Jeff MacDonald, VP of Support Services. "We expect to have the source code tested and ready to contribute to the open source community before the middle of October. Until that time we are considering requests from a number of development companies and venture capital groups to join us in this process." Davidson explains, "These initial Replication functions are important to almost every commercial user of PostgreSQL. While we've fully funded all of this development ourselves, we will be immediately donating these capabilities to the open source PostgreSQL Global Development Project as part of our ongoing commitment to the PostgreSQL community." http://www.erserver.com/ eRServer development is currently concentrating on core, universal functions that will enable individuals and IT professionals to implement PostgreSQL ORDBMS solutions for mission critical datawarehousing, datamining, and eCommerce requirements. These initial developments will be published under the PostgreSQL Open Source license, and made available through our sites, Certified Platinum Partners, and others in PostgreSQL community. Advanced Replication and Distributed Information capabilities are also under development to meet specific business and competitive requirements for both PostgreSQL, Inc. and clients. Several of these enhanced PostgreSQL, Inc. developments may remain proprietary for up to 24 months, with availability limited to clients and partners, in order to assist us in recovering development costs and continue to provide funding for our other Open Source contributions. On Sun, 3 Dec 2000, Hannu Krosing wrote: > The Hermit Hacker wrote: > IIRC, this thread woke up on someone complaining about PostgreSQl inc > promising > to release some code for replication in mid-october and asking for > confirmation > that this is just a schedule slip and that the project is still going on > and > going to be released as open source. > > What seems to be the answer is: "NO, we will keep the replication code > proprietary". > > I have not seen this answer myself, but i've got this impression from > the contents > of the whole discussion. > > Do you know if this is the case ? > > --- > Hannu >
[HACKERS] SQL to retrieve FK's, Update/Delete action, etc.
Given the name of a table, I need to find all foreign keys in that table and the table/column that they refer to, along with the action to be performed on update/delete. The following query works, but only when there is 1 foreign key in the table, when there is more than 2 it grows exponentially -- which means I am missing a join. However, given my limitied knowledge about the layouts of the postgres system tables, and the pg_trigger not being documented on the web site, I have been unable to get the correct query. Is this possible, and if so, what join(s) am I missing? SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred, pg_proc.proname, pg_proc_1.proname FROM pg_class pc, pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger, pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt WHERE pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid AND pg_trigger.tgconstrrelid = pc.oid AND pg_proc.oid = pg_trigger.tgfoid AND pg_trigger_1.tgfoid = pg_proc_1.oid AND pg_trigger_1.tgconstrrelid = pc.oid AND ((pc.relname='tblmidterm') AND (pp.proname LIKE '%ins') AND (pg_proc.proname LIKE '%upd') AND (pg_proc_1.proname LIKE '%del')) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
[HACKERS] RI Types
I am trying to set the update and delete rules that are returned from the ODBC driver and the spec has the following to say: SQL_NO_ACTION: If a delete of a row in the referenced table would cause a "dangling reference" in the referencing table (that is, rows in the referencing table would have no counterparts in the referenced table), then the update is rejected. (This action is the same as the SQL_RESTRICT action in ODBC 2.x.) What I need to know is if RI_FKey_noaction_del and RI_FKey_restrict_del procedures are functionally the same. The ODBC (which I would hope conforms to SQL 9x) spec has 4 types of RI (CASCADE, NO_ACTION, SET_NULL, SET_DEFAULT), and Postgres appears to have 5 (RI_FKey_cascade_del, RI_FKey_noaction_del, RI_FKey_restrict_del, RI_FKey_setdefault_del, RI_FKey_setnull_del), which leads me to belive that restrict and noaction are the same thing, and the one that is used depends on what the user puts in the REFERENCES line. Am I correct? Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Fri, 1 Dec 2000, Stephan Szabo wrote: > > It's representing a single null I believe. I'm not > sure if in general it's an octal or decimal number > but 3 digits for the value of the character. > > Stephan Szabo > [EMAIL PROTECTED] > > On Fri, 1 Dec 2000, Michael Fork wrote: > > > What are these characters: > > > > \000 > > > > are they 3 nulls? a null followed by 2 zeros? > > > > The reason I have been asking is that I am adding foreign key support to > > the ODBC driver :) > >
[HACKERS] ODBC Driver
I am curious as to where the newest ODBC driver source is -- I retrieved /src/interfaces/odbc from CVS, but it appeared to only be version 6.40.0009 and was lacking the Visual C++ workspace/project files that were in the 6.50. release zip file on the FTP server. Thanks Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
[HACKERS] pg_trigger and tgargs
I was wondering if someone could tell me if I have gotten the fields of tgargs correct: \000 -- Constraint name? foreign_table_multi\000 -- table with foreign key(s) primary_table_multi\000 -- table with primary key(s) UNSPECIFIED\000 -- ?? foreign_int_1\000 -- 1st field in foreign key primary_int_1\000 -- 1st field in referenced primary key foreign_int_2\000 -- 1st field in foreign key primary_int_2\000 -- 1st field in referenced primary key Thanks Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
Re: [HACKERS]
try this
SELECT age(max(h_date), now()) FROM table WHERE email='hawks@vsnl';
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 30 Nov 2000, Manish Vig wrote:
> Dear Sir,
> thanks for the reply.
> I tried select now()
> but it gives the following error
> syntax error near unexpected token `select.
>
> To be specific about my problem, I want to compare one max date with the
> current date in my Java servlet
> Since nested queries are not possible, how do i acheive my goal.
>
> My present query doesn't works and is like this
>
> Select months_between(('select max(h_date ) from query where
> email="[EMAIL PROTECTED]"),(select sysdate from dual)) from query
>
> What is the SQL query that can acheive the same effect.
> With Best Regards
> SanjayArora
>
>
>
[HACKERS] PHPBuilder article -- Postgres vs MySQL
Thought this may be of interest to some... http://www.phpbuilder.com/columns/tim20001112.php3 Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
[HACKERS] ORDER BY and UNION
Is the following expected behavior for a UNION query with ORDER BY:
executing this query:
+++
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text ELSE ''::text
END || 'default ' ||
CASE WHEN a.atthasdef = 't' THEN
substring(d.adsrc for 128)::text
ELSE ''::text END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_attrdef d
WHEREc.relname = 'tblplayer' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum
UNION ALL
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text
ELSE
''::text
END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t
WHEREc.relname = 'tblplayer' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
a.attname NOT IN (SELECT a.attname
FROM pg_class c,
pg_attribute a,
pg_attrdef d
WHERE c.relname = 'tblplayer' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum)
ORDER BY a.attnum;
+++
yields
number | attribute |type |modifier
+---+-+
1 | play_id | int4| not null default nextval('...
2 | play_name | varchar(30) | not null
3 | play_username | varchar(16) | not null
4 | play_password | varchar(16) | not null
5 | play_online | bool| default 'f'
However, if I execute the same query and drop "a.attnum as number" from
the select part, it returns the following:
attribute |type |modifier
---+-+
play_id | int4| not null default nextval('...
play_online | bool| default 'f'
play_name | varchar(30) | not null
play_username | varchar(16) | not null
play_password | varchar(16) | not null
which is incorrect accoring to the initial query. It appears to be
ordering the individual selects and then appending the second query to
the first -- is this correct?
Thanks
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
[HACKERS] Query returning incorrect results
When I execute the following two queries, the results differ -- with the only change being that another table is joined (a 1-1 join that should not affect the results -- I reduced down a much larger query that was exhibiting the behavior to what appears to be the cause). I know that views have some limitations, and two of the relations used are views, so I belive that that may be the problem, but I want to be sure... Thanks Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio football=# SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblweek_correct correct, tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = correct.play_id AND correct.game_week = 6 AND play.play_id = 4; play_id | ytd_correct_picks -+--- 4 | 141 (1 row) football=# SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = 4; play_id | ytd_correct_picks -+--- 4 |47 (1 row) football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblweek_correct correct, tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = correct.play_id AND correct.game_week = 6 AND play.play_id = 4; NOTICE: QUERY PLAN: Aggregate (cost=108.40..108.41 rows=0 width=64) -> Group (cost=108.40..108.40 rows=1 width=64) -> Sort (cost=108.40..108.40 rows=1 width=64) -> Nested Loop (cost=0.00..108.39 rows=1 width=64) -> Nested Loop (cost=0.00..106.36 rows=1 width=56) -> Nested Loop (cost=0.00..104.33 rows=1 width=52) -> Nested Loop (cost=0.00..16.54 rows=1 width=40) -> Nested Loop (cost=0.00..14.75 rows=1 width=36) -> Nested Loop (cost=0.00..13.30 rows=1 width=32) -> Nested Loop (cost=0.00..11.52 rows=1 width=28) -> Nested Loop (cost=0.00..5.11 rows=1 width=16) -> Seq Scan on tblgame game (cost=0.00..2.08 rows=1 width=8) -> Index Scan using tblgame_winner_pkey on tblgame_winner winner (cost=0.00..2.01 rows=1 width=8) -> Index Scan using tblpick_gameid_playid on tblpick pick (cost=0.00..5.05 rows=3 width=12) -> Seq Scan on tblplayer play (cost=0.00..1.35 rows=35 width=4) -> Seq Scan on tblplayer play (cost=0.00..1.44 rows=1 width=4) -> Seq Scan on tblplayer play (cost=0.00..1.35 rows=35 width=4) -> Seq Scan on tblpick pick (cost=0.00..51.24 rows=2924 width=12) -> Index Scan using tblgame_pkey on tblgame game (cost=0.00..2.01 rows=1 width=4) -> Index Scan using tblgame_winner_pkey on tblgame_winner winner (cost=0.00..2.01 rows=1 width=8) EXPLAIN football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id = 4; NOTICE: QUERY PLAN: Aggregate (cost=101.12..101.12 rows=0 width=32) -> Group (cost=101.12..101.12 rows=1 width=32) -> Sort (cost=101.12..101.12 rows=1 width=32) -> Hash Join (cost=96.27..101.11 rows=1 width=32) -> Seq Scan on tblgame_winner winner (cost=0.00..1.72 rows=72 width=8) -> Hash (cost=96.26..96.26 rows=4 width=24) -> Hash Join (cost=3.40..96.26 rows=4 width=24) -> Nested Loop (cost=0.00..91.02 rows=19 width=20) -> Nested Loop (cost=0.00..3.23 rows=1 width=8) -> Seq Scan on tblplayer play (cost=0.00..1.44 rows=1 width=4) -> Seq Scan on tblplayer play (cost=0.00..1.35 rows=35 width=4) -> Seq Scan on tblpick pick (cost=0.00..51.24 rows=2924 width=12) -> Hash (cost=1.86..1.86 rows=86 width=4) -> Seq Scan on tblgame game (cost=0.00..1.86 rows=86 width=4) EXPLAIN football=# \d tblweek_correct View "tblweek_correct" Attribute | Type | Modifier ---+-+
