[SQL] sql doubts
Could you help me ? 1- Whats the diference between TSQL and SQL ? 2- What are nested queries ? 3- I've seen people using DISTINCT, but what's ALL used for ? Thanks very much for your attention, Gustavo Henrique Maultasch [EMAIL PROTECTED]
[SQL] how to know when a table is altered
i'm developing a framework (mod_perl+apache) that reads the db-schema and explode html forms. now i read the schema and cache it into perl-hashes to speedup things. my problem is to recognise when a table is altered so that the framework can update the related forms connected to the db tables. i don't want to read the schema every time. How can i implement this ? thank you in advance for your help valter, italy Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Re: [SQL] sql doubts
On Wed, 7 Jun 2000, Gustavo Henrique wrote: Ad 1) SQL = Structured Query Language :) TSQL is an extention of SQL that includes support for queries against temporal databases. VALID/TRANSACTION TIME and SEQUENCED/NONSEQUENCED queries. If you would like more info on temporal databases try http://www.cs.auc.dk/TimeCenter which also has an on-line temporal database you can query using a java-applet (tiger) http://www.cs.auc.dk/~tigeradm > Could you help me ? > > 1- Whats the diference between TSQL and SQL ? > > 2- What are nested queries ? > > 3- I've seen people using DISTINCT, but what's ALL used for ? > > Thanks very much for your attention, > > > Gustavo Henrique Maultasch > [EMAIL PROTECTED] >
[SQL] Problem with subquery in CHECK constraint.
Hi,
I'm using a general lookup table defined as following
to avoid a proliferation of lookup tables:
CREATE TABLE XREF
(
XREF_GROUP VARCHAR(12) NOT NULL,
XREF_CD VARCHAR(8) NOT NULL,
XREF_VALUE VARCHAR(128),
PRIMARY KEY (XREF_GROUP, XREF_CD)
);
INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired');
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active');
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential');
I'm trying to define a check constraint to validate
lookup codes used, for example:
CREATE TABLE CUST
(
CUST_ID INTEGER NOT NULL,
NAMEVARCHAR(64) NOT NULL,
TYPE_CD VARCHAR(8) NOT NULL,
CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),
CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE
XREF_GROUP = 'CUST_TYPE' AND XREF_CD =
TYPE_CD) )
);
However when trying to insert into CUST I get the following error:
ERROR: ExecEvalExpr: unknown expression type 108
Is this a bug in PostGreSQL? I can work around it by
defining a function and using it in the CHECK constraint
for now. See the appended test file for example SQL.
By the way, there was a massive performance difference in using:
SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND
XREF_CD = $2)
versus the slower:
SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)
for validation, is this to be expected?
Here's the test case:
DROP TABLE XREF;
CREATE TABLE XREF
(
XREF_GROUP VARCHAR(12) NOT NULL,
XREF_CD VARCHAR(8) NOT NULL,
XREF_VALUE VARCHAR(128),
PRIMARY KEY (XREF_GROUP, XREF_CD)
);
INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired');
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active');
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential');
DROP FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8));
CREATE FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8)) RETURNS BOOLEAN AS
'SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND
XREF_CD = $2)'
LANGUAGE 'SQL';
--
-- Much slower version:
--
-- 'SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)'
--
DROP TABLE CUST;
CREATE TABLE CUST
(
CUST_ID INTEGER NOT NULL,
NAMEVARCHAR(64) NOT NULL,
TYPE_CD VARCHAR(8) NOT NULL,
CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),
CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE
XREF_GROUP = 'CUST_TYPE' AND XREF_CD =
TYPE_CD) )
);
INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'POT');
DROP TABLE CUST;
CREATE TABLE CUST
(
CUST_ID INTEGER NOT NULL,
NAMEVARCHAR(64) NOT NULL,
TYPE_CD VARCHAR(8) NOT NULL,
CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),
CONSTRAINT TYPE_CD_OK CHECK (VALID_XREF('CUST_TYPE', TYPE_CD))
);
INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'ACTIV');
INSERT INTO CUST VALUES (2, 'Jim Smith', 'foo');
--
Niall Smart
email: [EMAIL PROTECTED]
phone: (087) 8052390
[SQL] Temp table and loop call from one session
Hi all! I try to do: PL function 1 loop: call PL function 2 PL function 2 create temp table ... ... drop temp table But drop can't work in one session! It's documented. The method with create-drop temp table is most efficient for my problem. What can I do? Any idea? Dmitriy
Re: [SQL] how to know when a table is altered
Vincenzo Passoli wrote: > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > explode html forms. > > now i read the schema and cache it into perl-hashes to speedup things. > > my problem is to recognise when a table is altered so that the framework can > update the related forms connected to the db tables. > i don't want to read the schema every time. > > How can i implement this ? My sub-optimal approach was to cache all of the generally static tables (requiring a restart to reload them if they changed), and query the rest. You can avoid a lot of joins by querying the db for the foreign keys to static tables and then looking them up only in the app cache. But caching query results and invalidating them when the underlying tables changed would greatly simplify my app and speed things up, so I'd love to hear if others have a better/faster solution here. Regards, Ed Loehr
[SQL] oracle rownum equivalent?
is there an equivalent to rownum in oracle? also, where can one find reference to "hidden columns" such as OID or, as in oracle, rownum? thanks, mikeo
Re: [SQL] oracle rownum equivalent?
mikeo wrote: > > is there an equivalent to rownum in oracle? > > also, where can one find reference to "hidden columns" > such as OID or, as in oracle, rownum? oid is the equivalent. not sure documentation exists for these... Regards, Ed Loehr
[SQL] Please ignore [PSQL and PHP]
Hello,
Never mind the last email subjected "PSQL and PHP". I found the bug.
In PHP script, instead of using
insert into employee values (
'Bernie',
'{$phone, $fax, ...}'
);
or
insert into employee values (
'Bernie',
'{"$phone", "$fax", ...}'
);
or any other possible combo,
I should have used
insert into employee values (
'Bernie',
'{\"$phone\", \"$fax\", ...}'
);
Damn it! That makes me mad! Sorry about this.
- Bernie
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard
Re: [SQL] Problem with subquery in CHECK constraint.
Niall Smart <[EMAIL PROTECTED]> writes: > However when trying to insert into CUST I get the following error: > ERROR: ExecEvalExpr: unknown expression type 108 > Is this a bug in PostGreSQL? Yup. Looks like the subquery is not getting expanded into a subplan. Could be a little messy to fix, unless we redo the planning on every execution of the check constraint ... which doesn't sound appetizing. For now I'd suggest putting the subquery into an SQL or plpgsql function that you invoke from the constraint expression. regards, tom lane
Re: [SQL] how to know when a table is altered
Ed Loehr wrote: > > Vincenzo Passoli wrote: > > > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > > explode html forms. > > > > now i read the schema and cache it into perl-hashes to speedup things. > > > > my problem is to recognise when a table is altered so that the framework can > > update the related forms connected to the db tables. > > i don't want to read the schema every time. > > > > How can i implement this ? > > My sub-optimal approach was to cache all of the generally static tables > (requiring a restart to reload them if they changed), and query the > rest. You can avoid a lot of joins by querying the db for the foreign > keys to static tables and then looking them up only in the app cache. > But caching query results and invalidating them when the underlying > tables changed would greatly simplify my app and speed things up, so I'd > love to hear if others have a better/faster solution here. I was thinking about another possible approach (and definitely half-baked). I'd call it "table-based caching". Suppose you created a table specifically for tracking how recently a table had been updated, e.g., create table table_status ( tablename varchar not null unique, last_change timestamp not null ); Then create triggers for every table that updated table_status.last_change = now() on every UPDATE/DELETE/INSERT. Then, to determine when you need to invalidate the application cache, you'd load this table at the beginning of the request and invalidate cache entries involving tables with table_status.last_change more recent than when the query results were cached. If, like most DBs yours is mostly reads, you'd suffer one pretty light DB query in order to validate your cache on each request. Then, each That would be a significant hit on big changes involving many records. But where that's unusual, it might be a big win. There are a lot of gotchas with this approach (figuring out the query-table dependencies, etc.), but it seems possible. BTW, I thinking server-side caching is the optimal solution here. I previously lobbied -hackers for implementing a server-side result-set cache in which entire query result sets could be cached (up to a configurable limit) and returned immediately when none of the underlying tables had changed (http://www.deja.com/viewthread.xp?AN=602084610&search=thread). I still think that would be a huge performance win in the vast majority of systems (including mine), but it is not supposedly not trivial. The idea won absolutely no fans among the developers/hackers. There was some talk about caching the query plans, but I think that ultimately got dismissed as well. I wish I had time to work on this one. Regards, Ed Loehr
[SQL]
Reply-To: [EMAIL PROTECTED]
Subject: Re: [SQL] how to know when a table is altered
X-Mailer: NeoMail 0.95
X-IPAddress: 204.148.73.2
Ed wrote:
> Vincenzo Passoli wrote:
> > i'm developing a framework (mod_perl+apache) that reads the db-schema
and
> > explode html forms.
> > my problem is to recognise when a table is altered so that the framework
can
> > update the related forms connected to the db tables.
> > i don't want to read the schema every time.
> > How can i implement this ?
> My sub-optimal approach was to cache all of the generally static tables
> (requiring a restart to reload them if they changed), and query the
> rest. You can avoid a lot of joins by querying the db for the foreign
> keys to static tables and then looking them up only in the app cache.
> But caching query results and invalidating them when the underlying
> tables changed would greatly simplify my app and speed things up, so I'd
> love to hear if others have a better/faster solution here.
>
Personally, we did something similiar here. There is a textfile
that was used to create the schema, and so we read in that
into memory and then did an checked the parameters passed
in to see which tables were affected (the param('client_billing'
or such like). If you pass in parameters in this fashion, then
you can instantly rule out most tables from the update.
Once you have that, you need to check each value for a
change. Its not that slow (2-4 seconds roughly on a p2-266
with Sybase12) but there is a better way to do this. there
+must+ be ;)
of course, you could try a 'select' on the table with all those
values as match criteria, if you dont find an exact match then
insert it. otherwise dont. dont know how fast that will be though.
If you are only interested in certain 'key values' then why not
pollute the apache global space with an associative array ?
(assuming of course you are running apache and perl ;)
ymmv.
regards,
Stefs
Re: [SQL] oracle rownum equivalent?
thanks for the response. oid is equivalent to oracle rowid. rownum can be used similar to the limit option of select in postgres but in oracle it can also be used in a where clause, or as an assigment in an update statement for instance. eg: update ctmaster set bsc_id = mod(rownum,3) +1; this gives me a way to assign streams to rows in a load balanced manner on the fly, for example. i use it in other more involved ways than this also. i cannot do this with limit. i could do this with sequence with a max value but i'd have to define a sequence each time i wanted to do something "on the fly" or for what ifs. what i'm also interested in is how to find reference to these type of pseudo-columns, even just the names of them, if they're listed somewhere. thanks again, mikeo At 08:29 AM 6/7/00 -0500, Ed Loehr wrote: >mikeo wrote: >> >> is there an equivalent to rownum in oracle? >> >> also, where can one find reference to "hidden columns" >> such as OID or, as in oracle, rownum? > >oid is the equivalent. not sure documentation exists for these... > >Regards, >Ed Loehr >
Re: [SQL] oracle rownum equivalent?
mikeo wrote: > > thanks for the response. oid is equivalent to oracle rowid. > rownum can be used similar to the limit option of select in postgres > but in oracle it can also be used in a where clause, or as an assigment > in an update statement for instance. > > eg: update ctmaster set bsc_id = mod(rownum,3) +1; > > this gives me a way to assign streams to rows in a load balanced manner > on the fly, for example. i use it in other more involved ways than this > also. i cannot do this with limit. i could do this with sequence with > a max value but i'd have to define a sequence each time i wanted to do > something "on the fly" or for what ifs. > > what i'm also interested in is how to find reference to these type of > pseudo-columns, even just the names of them, if they're listed somewhere. I think this might be the list, but you might query pgsql-hackers for more info. There was a recent thread involving this.. ctid oid xmin (minimum transaction number) xmax cmin (minimum command number) cmax ctid may be what you're looking for, but I don't understand very well how these are used. Maybe someone else can say or you can experiment... Regards, Ed Loehr (PS: Posting to only one of -general or -sql will almost always be sufficient.)
[SQL] counting distinct values
Using the example from http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I do if I wanted to know the number of different cities where I had a friend in each state? select count(city) group by state; would not work because if you had two friends in the same city it would be counted twice.
RE: Re: [SQL] how to know when a table is altered
hello Ed Loehr,
1.your solution (table-based caching) is very close to my actual thinking.
2.Another problem is the 'alter table' command.
3.SQL does't have a TRIGGER on this 'event' (CREATE TRIGGER mytrig ON
mytable FOR ALTER AS ...). Can be Added ?
4.May be beautiful if the db tells to the app when a trigger is fired, so
the app can update thing without go crazy with asking that to the db every
time. Is there a solution?
5.For the query table dependencies (a proposal, i've not used this
solution!):
$sql= "select a.f1,a.f2,b.f3,c.f4 from t1 as a, t2 as b, t3 as c where
"
we can extract the tables used in a query
instead of writing $sql=as before, write a thing similar to (supposing
DBI+perl+mod_perl)
my @array;
my $ptr_array= \@array;
$sql = "select a.f1,a.f2,b.f3,c.f4 from
".&add_check_table('t1',$ptr_array)." as a,".
&add_check_table('t2',$ptr_array) . " as b,
---> &add_check_table=sub to push table to check in the array @array, return
the name of the table, i.e. t1, t2 ...
then
call &do_check ($ptr_array)
using table_status, the sub do_check return 1 if min(last_changes for every
table in @array) is older that the caching of this query results, we must
have the query result somewhere (on ( properly locked) files?) and the last
time we perfomed the query.
then
if (&do_check($ptr_array)){
fetch rows
store in cache
}
-->use the cache
Probably this solution must be used when is logical to be used.
bye,
valter
>From: Ed Loehr <[EMAIL PROTECTED]>
>To: Vincenzo Passoli <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
>Subject: Re: [SQL] how to know when a table is altered
>Date: Wed, 07 Jun 2000 11:49:55 -0500
>
>Ed Loehr wrote:
> >
> > Vincenzo Passoli wrote:
> > >
> > > i'm developing a framework (mod_perl+apache) that reads the db-schema
>and
> > > explode html forms.
> > >
> > > now i read the schema and cache it into perl-hashes to speedup things.
> > >
> > > my problem is to recognise when a table is altered so that the
>framework can
> > > update the related forms connected to the db tables.
> > > i don't want to read the schema every time.
> > >
> > > How can i implement this ?
> >
> > My sub-optimal approach was to cache all of the generally static tables
> > (requiring a restart to reload them if they changed), and query the
> > rest. You can avoid a lot of joins by querying the db for the foreign
> > keys to static tables and then looking them up only in the app cache.
> > But caching query results and invalidating them when the underlying
> > tables changed would greatly simplify my app and speed things up, so I'd
> > love to hear if others have a better/faster solution here.
>
>I was thinking about another possible approach (and definitely
>half-baked). I'd call it "table-based caching". Suppose you created a
>table specifically for tracking how recently a table had been updated,
>e.g.,
>
> create table table_status (
> tablename varchar not null unique,
> last_change timestamp not null
> );
>
>Then create triggers for every table that updated
>table_status.last_change = now() on every UPDATE/DELETE/INSERT. Then, to
>determine when you need to invalidate the application cache, you'd load
>this table at the beginning of the request and invalidate cache entries
>involving tables with table_status.last_change more recent than when the
>query results were cached.
>
>If, like most DBs yours is mostly reads, you'd suffer one pretty light DB
>query in order to validate your cache on each request. Then, each That
>would be a significant hit on big changes involving many records. But
>where that's unusual, it might be a big win. There are a lot of gotchas
>with this approach (figuring out the query-table dependencies, etc.), but
>it seems possible.
>
>BTW, I thinking server-side caching is the optimal solution here. I
>previously lobbied -hackers for implementing a server-side result-set
>cache in which entire query result sets could be cached (up to a
>configurable limit) and returned immediately when none of the underlying
>tables had changed
>(http://www.deja.com/viewthread.xp?AN=602084610&search=thread). I still
>think that would be a huge performance win in the vast majority of
>systems (including mine), but it is not supposedly not trivial. The idea
>won absolutely no fans among the developers/hackers. There was some talk
>about caching the query plans, but I think that ultimately got dismissed
>as well. I wish I had time to work on this one.
>
>Regards,
>Ed Loehr
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
[SQL] Fetch an element in an array
Hi,
This is more of a PHP problem, but I wish if someone knows this can help
me. How do I fetch an element in a Postgres array?
I have a table like
employee
col1 int4
col2 text[]
now I used the php api
$row = pg_fetch_array($exec, 0);
which gives me the following:
echo $row['col2'];
(prints out ===> {"123-4567", "Bernie", "123-5670"} )
(echo $row['col2[2]']; <=== didn't work)
What can I do to extract only the "Bernie" element?
Thanks.
- Bernie
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard
RE: [SQL] Problem with subquery in CHECK constraint.
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf > Of Tom Lane > > Niall Smart <[EMAIL PROTECTED]> writes: > > However when trying to insert into CUST I get the following error: > > ERROR: ExecEvalExpr: unknown expression type 108 > > Is this a bug in PostGreSQL? > > Yup. Looks like the subquery is not getting expanded into a subplan. > Could be a little messy to fix, unless we redo the planning on every > execution of the check constraint ... which doesn't sound appetizing. > There seems to be more serious problems. 1) The constraint is not only for the defined table but also for referenced tables in the subquery. 2) There should be some standard lock mechanism for the range restricted by the subquery. I'm suspicious that we should/could implement constraints other than column constraints. Regards. Hiroshi Inoue [EMAIL PROTECTED]
Re: [SQL] counting distinct values
> Using the example from > http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I > do if I wanted to know the number of different cities where I had a > friend in each state? select count(city) group by state; would not work > because if you had two friends in the same city it would be counted > twice. > How about? select city, state, count(*) group by state, city; -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] References and privileges
[EMAIL PROTECTED] (Jan Wieck) writes: >> If one does: >> >> [...] >> grant select on master to a_user; >> grant select, insert, update, delete on detail to a_user; >> >> then if login as "a_user" and does: >> >> insert into detail (id, master_id) values (1, 10); >> >> this will result in: "ERROR: master: Permission denied". >> >> This seems a bug to me ? Isn't it ? > Outch, > yes, we missed something here. Peter, you said you'll > probably work on the ACL stuff after 7.0. We need to > coordinate that work with the function manager redesign to go > for SETUID triggers and functions. Hey Jan: This one just bit me hard. We're trying to implement RI so a foreign key (employee ID number) is verified to exist in the employee table. Problem is, we have to grant everyone and their dog access to the employee table (select,update, no less) which contains all kinds of sensitive data about employees. We even tried making a limited view of the employee table and referencing that. No luck, the RI triggers try to find an OID column in the referenced table (view). If we could name a column "oid" in a view, we might have a chance with this approach. How hard would it be to get the "grant references" thing going? Is there anything I could do to help? Grant permissions on columns would also solve this problem. Is that one huge or not so huge? Kyle begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard
Re: [SQL] counting distinct values
Bruce Momjian wrote: > > > Using the example from > > http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I > > do if I wanted to know the number of different cities where I had a > > friend in each state? select count(city) group by state; would not work > > because if you had two friends in the same city it would be counted > > twice. > > > > How about? > > select city, state, count(*) group by state, city; OK that doesn't work for me because I want to use other agregates on the state level. Forgetting the state/city example. playpen=> create table tablem ( a int, b int, n int); CREATE playpen=> insert into tablem (a, b, n) values (1, 2, 1); INSERT 35197 1 playpen=> insert into tablem (a, b, n) values (1, 2, 2); INSERT 35198 1 playpen=> insert into tablem (a, b, n) values (1, 3, 1); INSERT 35199 1 playpen=> insert into tablem (a, b, n) values (1, 4, 3); INSERT 35296 1 playpen=> insert into tablem (a, b, n) values (1, 1, 4); INSERT 35297 1 playpen=> insert into tablem (a, b, n) values (2, 3, 3); INSERT 35298 1 playpen=> insert into tablem (a, b, n) values (2, 5, 7); INSERT 35299 1 playpen=> insert into tablem (a, b, n) values (2, 3, 3); INSERT 35300 1 playpen=> insert into tablem (a, b, n) values (2, 3, 1); INSERT 35301 1 playpen=> select a, count(*), avg(n), sum(n) from tablem group by a; a|count|avg|sum -+-+---+--- 1|5| 2| 11 2|4| 3| 14 (2 rows) ... now suppose I want to have the number of distictive b's in the results as well. I try: playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from tablem m group by a; ERROR: More than one tuple returned by a subselect used as an expression. playpen=> ... even though the subselect should only return one tuple.
Re: [SQL] how to know when a table is altered
Vincenzo Passoli wrote:
>
> 3.SQL does't have a TRIGGER on this 'event' (CREATE TRIGGER mytrig ON
> mytable FOR ALTER AS ...). Can be Added ?
I don't know. Maybe someone else does (though I think pgsql-sql is very
low volume...pgsql-general would get a lot more readers).
> 4.May be beautiful if the db tells to the app when a trigger is fired, so
> the app can update thing without go crazy with asking that to the db every
> time. Is there a solution?
Maybe. Check out NOTIFY (and LISTEN) at
http://www.postgresql.org/docs/postgres/sql-listen.htm
http://www.postgresql.org/docs/postgres/sql-notify.htm
I haven't tried it, not sure it fits into DBI's API or model. I'd like
to hear if you use it with success (or anyone else who is already using
it successfully within modperl/DBI).
If it turns out that DBI can't handle LISTEN/NOTIFY, it might be possible
to do it through a 3rd app that somehow listens and signals the modperl
servers (yuck).
> 5.For the query table dependencies (a proposal, i've not used this
> solution!):
>
> $sql= "select a.f1,a.f2,b.f3,c.f4 from t1 as a, t2 as b, t3 as c where
> "
> we can extract the tables used in a query
>
> instead of writing $sql=as before, write a thing similar to (supposing
> DBI+perl+mod_perl)
>
> my @array;
> my $ptr_array= \@array;
>
> $sql = "select a.f1,a.f2,b.f3,c.f4 from
> ".&add_check_table('t1',$ptr_array)." as a,".
>&add_check_table('t2',$ptr_array) . " as b,
>
> ---> &add_check_table=sub to push table to check in the array @array, return
> the name of the table, i.e. t1, t2 ...
>
> then
>
> call &do_check ($ptr_array)
> using table_status, the sub do_check return 1 if min(last_changes for every
> table in @array) is older that the caching of this query results, we must
> have the query result somewhere (on ( properly locked) files?) and the last
> time we perfomed the query.
>
> then
>
> if (&do_check($ptr_array)){
>fetch rows
>store in cache
> }
> -->use the cache
I haven't seen that syntax before with your use of "as", but I get your
gist. Sounds reasonable, though it looks like a major pain, stealing
most of the pleasure and convenience of SQL. I'd almost be tempted to
build a regex'er to pick out the table names from each query in a layer
between DBI and the app until the regex performance became an issue.
Regards,
Ed Loehr
Re: [SQL] counting distinct values
Joseph Shraibman <[EMAIL PROTECTED]> writes: Using the example from http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I do if I wanted to know the number of different cities where I had a friend in each state? select count(city) group by state; would not work because if you had two friends in the same city it would be counted twice. Er, what's wrong with select count(distinct city) group by state? > ... now suppose I want to have the number of distictive b's in the > results as well. I try: > playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select > count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from > tablem m group by a; > ERROR: More than one tuple returned by a subselect used as an > expression. > playpen=> > ... even though the subselect should only return one tuple. Not unless there's only one b value for any one a value --- otherwise the sub-select will return one row per b group. The error message looks correct to me. regards, tom lane
Re: [SQL] counting distinct values
Tom Lane wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > Using the example from > http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I > do if I wanted to know the number of different cities where I had a > friend in each state? select count(city) group by state; would not work > because if you had two friends in the same city it would be counted > twice. > > Er, what's wrong with select count(distinct city) group by state? I thought I tried that, but it didn't work the first time. Guess I mistyped something. Sorry. > > > ... now suppose I want to have the number of distictive b's in the > > results as well. I try: > > > playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select > > count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from > > tablem m group by a; > > ERROR: More than one tuple returned by a subselect used as an > > expression. > > playpen=> > > > ... even though the subselect should only return one tuple. > > Not unless there's only one b value for any one a value --- otherwise > the sub-select will return one row per b group. The error message looks > correct to me. > OK I thought that group by was eleminating duplicates which would then be counted. That evolved from q simple select(*) ... group by that worked. Don't know where I screwed it up.
RE: [GENERAL] Re: [SQL] oracle rownum equivalent?
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Ed Loehr > > mikeo wrote: > > > > thanks for the response. oid is equivalent to oracle rowid. IMHO,tid is equivalent to Oracle's rowid. > > rownum can be used similar to the limit option of select in postgres > > but in oracle it can also be used in a where clause, or as an assigment > > in an update statement for instance. > > > > eg: update ctmaster set bsc_id = mod(rownum,3) +1; > > > > this gives me a way to assign streams to rows in a load balanced manner > > on the fly, for example. i use it in other more involved ways than this > > also. i cannot do this with limit. i could do this with sequence with > > a max value but i'd have to define a sequence each time i wanted to do > > something "on the fly" or for what ifs. > > > > what i'm also interested in is how to find reference to these type of > > pseudo-columns, even just the names of them, if they're listed > somewhere. > > I think this might be the list, but you might query pgsql-hackers for > more info. There was a recent thread involving this.. > > ctid > oid > xmin (minimum transaction number) > xmax > cmin (minimum command number) > cmax > > ctid may be what you're looking for, but I don't understand very well how > these are used. Maybe someone else can say or you can experiment... > AFAIK,there's no pseudo-column like Oracle's rownum which is dynamically allocated at execution time. Regards. Hiroshi Inoue [EMAIL PROTECTED]
