Re: [SQL] Re: Problem with Dates
On Fri, 26 Jan 2001 12:46, Glen and Rosanne Eustace wrote: [ ... ] > template1=# select '31/12/2000'::date + '365 days'::timespan; > ?column? > > 2002-01-01 00:00:00+13<<< Wrong > (1 row) [ ... ] I get the same result. This business of crooked dates in NZ summertime is frequently because the rest of the world can't twig on to the notion that it is possible to have GMT + 13, and do not take account of it in date/time calculations. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [SQL] Re: Problem with Dates
Christopher Sawtell <[EMAIL PROTECTED]> writes: > On Fri, 26 Jan 2001 12:46, Glen and Rosanne Eustace wrote: > [ ... ] >> template1=# select '31/12/2000'::date + '365 days'::timespan; >> ?column? >> >> 2002-01-01 00:00:00+13<<< Wrong >> (1 row) > [ ... ] > I get the same result. This business of crooked dates in NZ summertime > is frequently because the rest of the world can't twig on to the notion that > it is possible to have GMT + 13, and do not take account of it in date/time > calculations. Could one of you try it in 7.1 (beta3 or later)? We've changed some details of the way daylight-savings transitions are handled in date-to-timestamp conversions, so I think this might be fixed now. It's worth checking anyway. Also, what do you get from '31/12/2000'::date::timestamp? regards, tom lane
Re: [SQL] Rule not invoked in 7.1
Tom Lane wrote: > Kyle <[EMAIL PROTECTED]> writes: > > If someone happens to know the primary key of a record they should not be > > able to access, and they try to update it, I would like the backend to > > ignore the query (or better yet, raise an exception but I haven't figured > > out how to do that). If the status is correct, the update should proceed. > > This might be better done with a trigger than a rule. For one thing, > a trigger can easily raise an exception. MHO is that rules are good > when you need to update multiple rows in other tables when certain > things happen. If you just want to validate or twiddle an individual > tuple as it's inserted/updated, a trigger is a good bet. But the trigger aproach requires access permissions to the base table in the first place, and exactly that's what Kyle want to restrict. Kyle, I doubt if you need the condition in the update rule at all. As far as I understood, your view restricts what the user can see from the base table. This restricted SELECT rule is applied to UPDATE events as well, so the UPDATE can never affect rows which are invisible through the view. create table t1 ( id integer, visible bool, datatext ); CREATE create view v1 as select id, data from t1 where visible; CREATE create rule upd_v1 as on update to v1 do instead update t1 set id = new.id, data = new.data where id = old.id; CREATE insert into t1 values (1, 't', 'Visible row'); INSERT 18809 1 insert into t1 values (2, 'f', 'Invisible row'); INSERT 18810 1 select * from v1; id |data +- 1 | Visible row (1 row) update v1 set data = 'Updated row'; UPDATE 1 select * from t1; id | visible | data +-+--- 2 | f | Invisible row 1 | t | Updated row (2 rows) update v1 set data = 'Updated row' where id = 2; UPDATE 0 select * from t1; id | visible | data +-+--- 2 | f | Invisible row 1 | t | Updated row (2 rows) As you see, neither an unqualified update of all rows, nor if the user guesses a valid id, can touch the invisible row filtered out by the view. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [SQL] "'" in SQL INSERT statement
Saluton!
On Thu, Jan 25, 2001 at 11:12:34AM +0100, Markus Wagner wrote:
...
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
>
> How can I put these into my database using the INSERT statement?
I always use this sub:
#
# This sub adapted from sub TEXT of mmusic by [EMAIL PROTECTED], using
# advice from the pgsql-novice mailing list (David Rugge, 1 Aug 1999).
#
# Do the necessary quoting for strings.
#
sub stdstr {
my $or = $_[0];
return undef unless (defined($or));
$or =~ s /\\//g;# mmusic doesn't have this, nor does D. Rugge.
$or =~ s /\'/\\\'/g;
$or =~ s /\"/\\\"/g;
$or =~ s /%/\\%/g; # mmusic doesn't have this.
return $or;
}
Obviously you also need to escape the \. I no longer remember why "
and % are needed, but I certainly did have some reason then.
Albert.
--
--
Albert Reiner <[EMAIL PROTECTED]>
Deutsch * English * Esperanto * Latine
--
[SQL] Re: abstract data types?
Mr. Reid, > To answer your question, it is a bit hard to say at the moment as the > design schema for our project has only just been started. The draft > versions of the ISO standard that I have seen use an object oriented > data model, so to me it makes sense to try and keep the database schema > as close as possible to this (minimise data impedance). > > Briefly, at its' simplest the schema will probably use a two tier approach. Let me preface this by saying that I know squat-all about building geometric databases. My background is in db's for accounting, billing, scheduling, and fundraising. Given that .., over the last 3 months, I have become a believer in C.J. Date and Fabian Pascal, who point out quite a few ways that object-oriented and relational approaches to data problems *cannot* be made to reconcile. See http://www.firstsql.com/dbdebunk for some examples of their objections. Of course, Date and Pascal reject Object Oriented approaches entirely, something I'm not ready to do ... but I do see that trying to build a database accessable to both a range of OODB tools and relationally compliant is not achievable. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Rule not invoked in 7.1
Tom Lane wrote: > Kyle <[EMAIL PROTECTED]> writes: > > If someone happens to know the primary key of a record they should not be > > able to access, and they try to update it, I would like the backend to > > ignore the query (or better yet, raise an exception but I haven't figured > > out how to do that). If the status is correct, the update should proceed. > > This might be better done with a trigger than a rule. For one thing, > a trigger can easily raise an exception. MHO is that rules are good > when you need to update multiple rows in other tables when certain > things happen. If you just want to validate or twiddle an individual > tuple as it's inserted/updated, a trigger is a good bet. > The underlying table contains payroll data. Each employee should be able to enter payroll requests, but there is a very strict set of rules about which rows he should be able to access, how he can access them, and when. For example, an employee can enter new time records, but once the records have been approved or paid, he can no longer modify them. I have set up several views that allow access to the rows depending on their status. For example, employees only have access to "working records." Once they are satisfied with the data they have entered, they change the status to "open" at which point they can no longer edit it. Supervisors then have access to the record and can approve it, changing its status to "approved" and so on. The problem I had with trying to use a trigger was that the trigger fires on the underlying table, regardless of which view the user comes in on. (At least it seemed that way when I tested it.) I need to apply a different set of rules based on which view the user is coming in on--not simply who the user is. Is there a way to configure a trigger to fire on a view rather than the underlying table? I tried linking a trigger to a view but it seemed to not get called at all. I assumed this was because the rewriting rules were directing the query away from the view class and so the trigger was never getting called at all. begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
Re: [SQL] Rule not invoked in 7.1
Tom Lane wrote: Kyle <[EMAIL PROTECTED]> writes: > If someone happens to know the primary key of a record they should not be > able to access, and they try to update it, I would like the backend to > ignore the query (or better yet, raise an exception but I haven't figured > out how to do that). If the status is correct, the update should proceed. This might be better done with a trigger than a rule. For one thing, a trigger can easily raise an exception. MHO is that rules are good when you need to update multiple rows in other tables when certain things happen. If you just want to validate or twiddle an individual tuple as it's inserted/updated, a trigger is a good bet. I have another interesting use of this same concept you may be interested in (or slightly nausious, as the case may be): The underlying database (empl) contains all employees in the organization. The view empl_v_sup calls a recursive function to determine if a given employee works for the current user (either directly, or anywhere under him in the company heirarchy). The view only includes employees that work under the that user. There is also an exception for users who have certain types of privileges who get to see the whole company. This dynamic view is very cool as it allows different people to see different data in the same view depending on who they are, and how the hierarchical data is arranged in the employee database. -- Determine if an employee has another employee as his supervisor. -- An employee is, by definition, not his own supervisor -- Returns true or false -- calling sequence: _empl_ancest(employee,ancestor,level) create function _empl_ancest(int4,int4,int4) returns boolean as ' declare trec record; begin if $3 > 15 then raise exception \'Supervisor loop found on employee %\', $1; end if; -- a person can not be his own supervisor -- also if null or 0, we reached top of the ladder so return false if $1 = $2 or $1 is null or $1 = 0 then return false; end if; -- get the employees record select * into trec from empl_pub where empl_id = $1; if not found then raise exception \'Record not found for employee %\', $1; end if; -- if he is his own supervisor, we have probably reached the top so false if trec.superv = $1 then return false; end if; -- if his supervisor is the ancestor, return true if trec.superv = $2 then return true; end if; -- else check the parent recursively return _empl_ancest(trec.superv, $2, $3+1); end;' language 'plpgsql'; -- Determine if an employee has another employee as his ancestor. -- This adds a level parm to prevent infinite recursion. -- calling sequence: empl_ancest(employee,ancestor) create function empl_ancest(int4,int4) returns boolean as ' select _empl_ancest($1,$2,0); ' language 'sql'; --View with limited privileges for supervisors to see their own people create view empl_v_sup as select *,oid as _oid from empl where exists (select * from priv where empl_id = getpguid() and ((priv = 'emplim' and alevel = 'super') or (priv = 'payroll'))) or empl_ancest(empl_id,getpguid()); --Only the emplim-super can insert records create rule empl_v_sup_innull as on insert to empl_v_sup do instead nothing; create rule empl_v_sup_insert as on insert to empl_v_sup where (select count(*) from priv where priv = 'emplim' and alevel = 'super' and empl_id = getpguid()) > 0 do instead insert into empl (empl_id,pertitle,surname,givnames,prefname,jobtitle,addr,city,state,zip,country,phone,workph,mobile,email,ssn,bday,hiredate,termdate,lrevdate,nrevdate,paytyp,empltyp,superv,proxy,status,mstat,payrate,allow,wccode,eic,cmt) values (new.empl_id,new.pertitle,new.surname,new.givnames,new.prefname,new.jobtitle,new.addr,new.city,new.state,new.zip,new.country,new.phone,new.workph,new.mobile,new.email,new.ssn,new.bday,new.hiredate,new.termdate,new.lrevdate,new.nrevdate,new.paytyp,new.empltyp,new.superv,new.proxy,new.status,new.mstat,new.payrate,new.allow,new.wccode,new.eic,new.cmt); --Emplim-super can update any field create rule empl_v_sup_upnull as on update to empl_v_sup do instead nothing; create rule empl_v_sup_update as on update to empl_v_sup where (select count(*) from priv where priv = 'emplim' and alevel = 'super' and empl_id = getpguid()) > 0 do instead update empl set empl_id = new.empl_id, pertitle = new.pertitle, surname = new.surname, givnames = new.givnames, prefname = new.prefname, jobtitle = new.jobtitle, addr = new.addr, city = new.city, state = new.state, zip = new.zip, country = new.country, phone = new.phone, workph = new.workph, mobile = new.mobile, email = new.email, ssn = new.ssn, bday = new.bday, hiredate = new.hiredate, termdate = new.termdate, lrevdate = new.lrevdate, nrevdate = new.nrevdate, paytyp = new.paytyp, empltyp where empl_id = old.empl_id; --Emplim-user can update certain fields create rule empl_v_sup_update1 as on upda
Re: [SQL] Rule not invoked in 7.1
Jan Wieck wrote: > Tom Lane wrote: > > > This might be better done with a trigger than a rule. For one thing, > > a trigger can easily raise an exception. MHO is that rules are good > > when you need to update multiple rows in other tables when certain > > things happen. If you just want to validate or twiddle an individual > > tuple as it's inserted/updated, a trigger is a good bet. > > But the trigger aproach requires access permissions to the > base table in the first place, and exactly that's what Kyle > want to restrict. That's right. > Kyle, I doubt if you need the condition in the update rule at > all. As far as I understood, your view restricts what the > user can see from the base table. This restricted SELECT rule > is applied to UPDATE events as well, so the UPDATE can never > affect rows which are invisible through the view. This hadn't occurred to me but makes sense now that you say it. Taking that into consideration will make my job a bit simpler. The only complication is that there are a class of records which the user should be able to view, but not modify. For example, the employee can create and modify working records as long as the only modification to their status is to move them on to "open status" (creating an "approved" record would be a bad idea.) But the user should be able to view all their records (working, open, approved, and even paid). Hence, the restrictions on update are more stringent than those on select. begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
[SQL] Request for change in PL/PGSQL function handler
Jan, Tom, etc: Currently (7.0.3) the PLPGSQL function compiler permits only one RETURN statment, at the end of the function. RETURN statements placed elsewhere cause a compile error. This, combined with the lack of an ELSEIF statement, has forced me into sometimes 7 levels of nested IF..THEN statements. WHile they work fine, they're a bit hard to read and edit. For example, say I want to test for a, b, c, or d sequentially, under the 7.0.3 compiler, I must: BEGIN IF a THEN return_text := 'One'; ELSE IF b THEN return_text := 'Two'; ELSE IF c THEN return_text := 'Three'; ELSE IF d THEN return_text := 'Four'; ELSE return_text := 'Not Found'; END IF; END IF; END IF; END IF; RETURN return_text; END; As you can see, this kind of structure gets kind of had to read and maintain for more complex statments. I have two suggested revisions to the compiler that would make this much easier: SUGGESTION A: Support of an ELSEIF statement, as: IF a THEN return_text := 'One'; ELSEIF b THEN return_text := 'Two'; ELSIF c THEN return_text := 'Three'; ...etc. SUGGESTION B: Allow more than one RETURN statment in the function text, with funciton processing to terminate as soon as a RETURN is reached in the program logic, but otherwise be ignored: IF a THEN RETURN 'One'; END IF; IF b THEN RETURN 'Two'; END IF; ...etc. Both approaches would, from my perspective, make my code easier to read and maintain. And, of course, you may have already implemented one or the other in 7.1 (which I have not yet got to run on an alternate port). Thanks for your hard work and consideration towards us users. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Re: Problem with Dates
> Could one of you try it in 7.1 (beta3 or later)? We've changed some > details of the way daylight-savings transitions are handled in > date-to-timestamp conversions, so I think this might be fixed now. > It's worth checking anyway. I would prefer not to upgrade right at the moment, as my development system is in the middle of a migration exercise ( hence the discovery of this bug ). If some one else is running 7.1 already and can just change their timezone to New Zealand DT and report the results it would be great. > Also, what do you get from '31/12/2000'::date::timestamp? template1=# select '31/12/2000'::date::timestamp template1-# ; ?column? 2001-01-01 00:00:00+13 (1 row) template1=#
Re: [SQL] Re: Problem with Dates
"Glen and Rosanne Eustace" <[EMAIL PROTECTED]> writes: > If some one else is running 7.1 already and can just change their > timezone to New Zealand DT and report the results it would be great. Well, with TZ set to NZST-12NZDT I get regression=# select '31/12/2000'::date::timestamp; ?column? 2000-12-31 00:00:00+13 (1 row) regression=# select '31/12/2000'::date + '365 days'::timespan; ?column? 2001-12-31 00:00:00+13 (1 row) This looks promising but I wouldn't call it conclusive, particularly since you're probably using a different OS than I am (I'm on HPUX 10.20). It would be good to bang on it some more with NZ daylight times --- as Christopher says, GMT+13 is a tad unusual. regards, tom lane
Re: [SQL] Re: Problem with Dates
On Sat, 27 Jan 2001, Glen and Rosanne Eustace wrote: > If some one else is running 7.1 already and can just change their > timezone to New Zealand DT and report the results it would be great. Here ya are: cp=> set time zone 'NZ'; SET VARIABLE cp=> select '12/31/2000'::date + '1 year'::interval; ?column? 2001-12-31 00:00:00+13 (1 row) cp=> -- Brett http://www.chapelperilous.net/~bmccoy/ --- It's a good thing we don't get all the government we pay for.
Re: [SQL] Re: Problem with Dates
> regression=# select '31/12/2000'::date + '365 days'::timespan; > ?column? > > 2001-12-31 00:00:00+13 > (1 row) > > This looks promising but I wouldn't call it conclusive, particularly > since you're probably using a different OS than I am (I'm on HPUX > 10.20). It would be good to bang on it some more with NZ daylight > times --- as Christopher says, GMT+13 is a tad unusual. Is 7.0.3 to 7.1B? simply a reinstall or do I need to unload/reload the database. PS: GMT+13 isn't unusual for us Kiwis, that's how its always been since some twit decided daylight saving was a good idea. The jury is still out on this one. Glen.
Re: [SQL] Re: Problem with Dates
On Sat, 27 Jan 2001, Glen and Rosanne Eustace wrote: > Is 7.0.3 to 7.1B? simply a reinstall or do I need to unload/reload the > database. Yep, you need to do whole shebang of dumping and reloading. -- Brett http://www.chapelperilous.net/~bmccoy/ --- It's a good thing we don't get all the government we pay for.
[SQL]
Kyle wrote:
> The only complication is
> that there are a class of records which the user should be able to view, but not
> modify. For example,
> the employee can create and modify working records as long as the only
> modification to their status
> is to move them on to "open status" (creating an "approved" record would be a bad
> idea.)
>
> But the user should be able to view all their records (working, open, approved,
> and even paid).
>
> Hence, the restrictions on update are more stringent than those on select.
Ah.
Describe the entire problem and you'll get a complete answer:
CREATE TABLE wr_table (
w_idserial PRIMARY KEY,
w_user name,
w_state text,
w_data text
);
CREATE
CREATE VIEW wr_view AS
SELECT * FROM wr_table WHERE w_user = CURRENT_USER;
CREATE
CREATE RULE wr_view_ins AS ON INSERT TO wr_view DO INSTEAD
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES (
CURRENT_USER,
'OPEN',
new.w_data
);
CREATE
CREATE RULE wr_view_upd AS ON UPDATE TO wr_view DO INSTEAD
UPDATE wr_table SET w_data = new.w_data
WHERE w_id = old.w_id AND w_state = 'OPEN';
CREATE
CREATE RULE wr_view_del AS ON DELETE TO wr_view DO INSTEAD
DELETE FROM wr_table
WHERE w_id = old.w_id AND w_state = 'OPEN';
CREATE
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'OPEN', 'Open item 1 of pgsql');
INSERT 19392 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'OPEN', 'Open item 2 of pgsql');
INSERT 19393 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'CLOSED', 'Closed item 3 of pgsql');
INSERT 19394 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('someone', 'OPEN', 'Open item of someone else');
INSERT 19395 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('someone', 'CLOSED', 'Closed item of someone else');
INSERT 19396 1
SELECT CURRENT_USER;
current_user
--
pgsql
(1 row)
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Open item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item 2 of pgsql'
WHERE w_id = 2;
UPDATE 1
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item of someone else'
WHERE w_id = 4;
UPDATE 0
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item 3 of pgsql'
WHERE w_id = 3;
UPDATE 0
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
DELETE FROM wr_view;
DELETE 2
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(3
Re: [SQL] Rule not invoked in 7.1
Kyle wrote:
> The only complication is
> that there are a class of records which the user should be able to view, but not
> modify. For example,
> the employee can create and modify working records as long as the only
> modification to their status
> is to move them on to "open status" (creating an "approved" record would be a bad
> idea.)
>
> But the user should be able to view all their records (working, open, approved,
> and even paid).
>
> Hence, the restrictions on update are more stringent than those on select.
Ah.
Describe the entire problem and you'll get a complete answer:
CREATE TABLE wr_table (
w_idserial PRIMARY KEY,
w_user name,
w_state text,
w_data text
);
CREATE
CREATE VIEW wr_view AS
SELECT * FROM wr_table WHERE w_user = CURRENT_USER;
CREATE
CREATE RULE wr_view_ins AS ON INSERT TO wr_view DO INSTEAD
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES (
CURRENT_USER,
'OPEN',
new.w_data
);
CREATE
CREATE RULE wr_view_upd AS ON UPDATE TO wr_view DO INSTEAD
UPDATE wr_table SET w_data = new.w_data
WHERE w_id = old.w_id AND w_state = 'OPEN';
CREATE
CREATE RULE wr_view_del AS ON DELETE TO wr_view DO INSTEAD
DELETE FROM wr_table
WHERE w_id = old.w_id AND w_state = 'OPEN';
CREATE
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'OPEN', 'Open item 1 of pgsql');
INSERT 19392 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'OPEN', 'Open item 2 of pgsql');
INSERT 19393 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('pgsql', 'CLOSED', 'Closed item 3 of pgsql');
INSERT 19394 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('someone', 'OPEN', 'Open item of someone else');
INSERT 19395 1
INSERT INTO wr_table (w_user, w_state, w_data)
VALUES ('someone', 'CLOSED', 'Closed item of someone else');
INSERT 19396 1
SELECT CURRENT_USER;
current_user
--
pgsql
(1 row)
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Open item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item 2 of pgsql'
WHERE w_id = 2;
UPDATE 1
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item of someone else'
WHERE w_id = 4;
UPDATE 0
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
UPDATE wr_view SET w_data = 'Changed item 3 of pgsql'
WHERE w_id = 3;
UPDATE 0
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
1 | pgsql | OPEN| Open item 1 of pgsql
2 | pgsql | OPEN| Changed item 2 of pgsql
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(5 rows)
DELETE FROM wr_view;
DELETE 2
SELECT * FROM wr_table ORDER BY w_id;
w_id | w_user | w_state | w_data
--+-+-+-
3 | pgsql | CLOSED | Closed item 3 of pgsql
4 | someone | OPEN| Open item of someone else
5 | someone | CLOSED | Closed item of someone else
(3
Re: [SQL] Request for change in PL/PGSQL function handler
Josh Berkus <[EMAIL PROTECTED]> writes: > Currently (7.0.3) the PLPGSQL function compiler permits only one RETURN > statment, at the end of the function. RETURN statements placed > elsewhere cause a compile error. Say what? regression=# create function foo(int) returns int as ' regression'# begin regression'# if $1 > 10 then return $1; regression'# end if; regression'# return $1 - 1; regression'# end;' language 'plpgsql'; CREATE regression=# select foo(1); foo - 0 (1 row) regression=# select foo(100); foo - 100 (1 row) regression=# Works fine for me in both 7.0.2 and current. regards, tom lane
Re: [SQL] Request for change in PL/PGSQL function handler
Tom, > Say what? > > regression=# create function foo(int) returns int as ' > regression'# begin > regression'# if $1 > 10 then return $1; > regression'# end if; > regression'# return $1 - 1; > regression'# end;' language 'plpgsql'; > CREATE Hmmm? When I've tried creating similar functions, I got from the compiler: Error at or near 'END' I'll try your code above as a test, then try re-modifying some of my own functions. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Re: Problem with Dates
> Yep, you need to do whole shebang of dumping and reloading. Hmmm. I think I'll just cheat for a while and add 364 days, hopefully before the end of NZ daylight savings, the production release of 7.1 will be out. I really don't want to upgrade twice. Glen.
Re: [SQL] Re: Problem with Dates
On Sat, 27 Jan 2001 06:44, Glen and Rosanne Eustace wrote: > > Could one of you try it in 7.1 (beta3 or later)? ok, I'll have a go at upgrading. I'll try to get it done either this evening or tomorrow. Hopefully ready on Monday. I built from sources of v-7.0.0 are there some patches somewhere, or do I have to d/l the whole thing via cvs? btw, what's the name of the branch? While we are fiddling with time and zone etc. I have the problem here that the relationship between the date and the day of the week is out by one day too. There should be something about this in the mail archives about 6 to 9 months ago iirc. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [SQL] Re: Problem with Dates
Christopher Sawtell <[EMAIL PROTECTED]> writes: > I built from sources of v-7.0.0 are there some patches somewhere, or do I > have to d/l the whole thing via cvs? There's no patch (if there were, it'd be enormous). Either pull from CVS or use a beta or nightly-snapshot tarball (see pub/dev on our ftp server). > btw, what's the name of the branch? No branch, it's the tip... > While we are fiddling with time and zone etc. I have the problem here that > the relationship between the date and the day of the week is out by one day > too. There should be something about this in the mail archives about 6 to 9 > months ago iirc. Hm. Please re-submit details if this is still there. regards, tom lane
[SQL] Wild Cards
I am not able to get Wildcards in PostgreSQL, I know its * (asterisk), but its not working. can someone show me a example or something?
[SQL] how to query this ??
Hi everybody I have quite a query to build and don't know, whether this can be done with SQL. Please see the attachment for the details. Any help is really appreciated !! Thanks in advance jr (See attached file: queryDetails.doc) PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 queryDetails.doc
[SQL] SQL function and composite types
Hi all, having this simple table: create table foo (attribute text); and this simple function: create function f() returns foo as ' select ' 'test' '::text ' langauge 'sql' ; When I execute (though psql) select f(); I get the following result f 136411800 instead of the expected: f - test Any ideas?
[SQL]
I'm trying to use the 'bit' and 'varbit' datatypes in a table. The demo file 'varbit.demo.sql' runs fine but when I try to INSERT data into the table I get a message: ERROR: zpbit_in: bit string of size 8 cannot be written into bits(1) I can add data to the table through the console a la demo file though. I can also write one binary digit (i.e. 'B1') using INSERT but I cannot write longer bit strings - neither can I find a way to make the type of the column to be a longer bit string. Any suggestions? (I get the same problem with the varbit data type) David Brook Asst. Professor, Department of Chemistry and Biochemistry University of Detroit Mercy ph: (313) 993-2495 fax: (313) 993-1144David Brook Asst. Professor, Department of Chemistry and Biochemistry University of Detroit Mercy ph: (313) 993-2495 fax: (313) 993-1144
Re: [SQL] #DELETED error when using Access 2000 as frontend
> > I want to use pg 7.x as a backend for a MS Access application. I linked a > > table via ODBC, using the newest ODBC driver. I can open and view tables. But > > after I insert a new record, all fields will contain "#deleted". When I > > reopen the table, the inserted data is displayed correctly. > I recall seeing an MS Access 2000 bug that will cause this. I don't remember I can confirm the problem exists at least in access'97 -- applying both of the office'97 service packs helped. I have noticed all kinds of weird errors in Access which makes it operate badly with Postgres and I really can't recommend it as a frontend application, although I know there are people who use it quite successfully. On a side note - pls reply privately - what Win frontends (apart from web) have people used with Postgres, which work OK for them? Emils -- Emils Klotins IT Manager, Baltic states Grafton Entertainment Ltd. / TVNET 40-43 Brivibas Str., Riga LV1050, Latvia +371-7-242-001
Re: [SQL] DATE
> PLEASE ADVISE HOW I SHOULD ALTER THE COMMAND: > > <$NOW;DD;> > > TO GIVE ME A DATE THAT IS X DAYS FORWARD > > ie: I WISH TO REPRESENT A DATE IN FORM WHICH IS A 7 DAYS FORWARD > OF THE DATE NOW. ( now() + '7 days'::interval )::date (or ::datetime, whichever you need) Emils
[SQL] Invoking sql functions through jdbc
Hi all, I am trying to invoke a postgres sql function though JDBC using the CallableStatment object. I have build the driver with it's jdbc1.2 option. However, I am getting the following exception "Callable statments are not supported at this time" Does any one have an idea if there is a work around it? I have noticed that there is something called the Fastpath API. Is there any way to get a result set (and not byte[]) out of it? thanks Ron
[SQL] SQL Help
I am having trouble with the following query taking forever: - SELECT * FROM ret108108_00, product WHERE ret108108_00."isbn" = product."Item1" AND product."SuperCategory" = '1' AND product."PublisherCode" = 'ZON' ORDER BY ret108108_00.qty DESC LIMIT 100 The problem is the second AND -- if I take this out it runs fine -- is there any way to optimize it ? I had a similar problem with just Category so I created a new field in the product file called FullCat which combined SuperCategory and Category and thus eliminating the 2nd AND clause -- and that fixed it -- I just didn't want to do the same thing with Publisher. Before I tried to index Category, SuperCategory ... nothing seemed to help Any ideas Thanks in advance Mark Summers
[SQL] Re: Is there anything like DESCRIBE?
I got a response in email, so I assume the author wants to remain anonymous. He wrote: >There's an option to psql (I think -E or -e) that will make it echo all >the SQL queries it does when you do something like "\d table" So running a "psql -E " and then doing a "\d " will give you information similar to what you would normally get from a DESCRIBE. Mike -- "Mike D'Agosta" <[EMAIL PROTECTED]> wrote in message 94n93j$2j6j$[EMAIL PROTECTED]">news:94n93j$2j6j$[EMAIL PROTECTED]... > Hi, > >I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? > > Thanks! > Mike
Re: [SQL] Wild Cards
On Thu, 25 Jan 2001, wrote: > I am not able to get Wildcards in PostgreSQL, I know its * (asterisk), but > its not working. can someone show me a example or something? Wildcards where? You can use * to mean all the fields in a table in a SELECT statement, but if you are using LIKE in a WHERE clause, the wildcards are % to mean any group of characters and _ to mean any single character. -- Brett http://www.chapelperilous.net/~bmccoy/ --- It's a good thing we don't get all the government we pay for.
Re: [SQL] #DELETED error when using Access 2000 as frontend
these are common errors on the mysql side also. Most likely poor planing on the Access end. Emils Klotins wrote: > > > I want to use pg 7.x as a backend for a MS Access application. I linked a > > > table via ODBC, using the newest ODBC driver. I can open and view tables. But > > > after I insert a new record, all fields will contain "#deleted". When I > > > reopen the table, the inserted data is displayed correctly. > > I recall seeing an MS Access 2000 bug that will cause this. I don't remember > I can confirm the problem exists at least in access'97 -- applying > both of the office'97 service packs helped. > > I have noticed all kinds of weird errors in Access which makes it > operate badly with Postgres and I really can't recommend it as a > frontend application, although I know there are people who use it > quite successfully. > > On a side note - pls reply privately - what Win frontends (apart from > web) have people used with Postgres, which work OK for them? > > Emils > > -- > Emils Klotins > IT Manager, Baltic states > Grafton Entertainment Ltd. / TVNET > 40-43 Brivibas Str., Riga LV1050, Latvia > +371-7-242-001
Re: [SQL] SQL Help
On Fri, 26 Jan 2001, Mark A. Summers wrote: > I am having trouble with the following query taking forever: > - > SELECT * FROM ret108108_00, product > WHERE ret108108_00."isbn" = product."Item1" > > AND product."SuperCategory" = '1' > AND product."PublisherCode" = 'ZON' > ORDER BY ret108108_00.qty DESC LIMIT 100 > > The problem is the second AND -- if I take this out it runs fine -- is there > any way to optimize it ? I had a similar problem with just Category so I > created a new field in the product file called FullCat which combined > SuperCategory and Category and thus eliminating the 2nd AND clause -- and > that fixed it -- I just didn't want to do the same thing with Publisher. > Before I tried to index Category, SuperCategory ... nothing seemed to help Have you run a VACUUM ANALYZE on the table? And what does EXPLAIN show for the query?
Re: [SQL] Wild Cards
On Thu, Jan 25, 2001 at 02:29:55PM -0500, Brett W. McCoy wrote: > On Thu, 25 Jan 2001, wrote: > > > I am not able to get Wildcards in PostgreSQL, I know its * (asterisk), but > > its not working. can someone show me a example or something? > > Wildcards where? You can use * to mean all the fields in a table in a > SELECT statement, but if you are using LIKE in a WHERE clause, the > wildcards are % to mean any group of characters and _ to mean any single > character. Although, of course, you can use POSIXlish regexps with the ~* and ~ operators. -- Rodger Donaldson[EMAIL PROTECTED] "My mother made me a lesbian" "Oh goody! If I buy her the wool, will she make me one too??"
Re: [SQL]
"David J. R. Brook" <[EMAIL PROTECTED]> writes: > I'm trying to use the 'bit' and 'varbit' datatypes in a table. What version are you running? IIRC, there are types calling themselves bit and varbit in 7.0, but they're only stubs; the first real support for bitstrings is in 7.1. regards, tom lane
