Re: [SQL] functions that return a dataset or set of rows
Is it not possible in 7.2? Gaetano Mendola wrote: "Brian Ward" <[EMAIL PROTECTED]> wrote in message news:aofqbd$10v5$1@;news.hub.org... How do I create a function that returns a set of row; I can't seem to find the datatype that the return set should be declared as. You should wait for Postgres 7.3 currently in beta. Ciao Gaetano ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Cascading a pseudo-delete?
Greetings, for our database, we keep everything--deleting nothing; however, to the client side, it should appear deleted. We do this via a status_cd for each record. If the status_cd is for 'deleted' then that item is not shown to the client-side. Anyhow, my question is that we also use foreign keys, and I would like to delete the one record and have the deletion cascade to all records using that foreign key. This is no problem, until I try to no longer delete, but, change the flag. So, my question is, how can a create a rule which activates on delete and simply marks the record deleted then cascade that deletion to all of the objects who are constrained to its key? Thanks, --Brian
[SQL] Permissions Broken in 7.0.x?
Greetings, I am having problem with table permissions and view permissions. Say I am user bob and I own a database, bobdb. Let us also assume a user, tester has been added to the db, via create user... % psql bobdb psql => create table test ( name char(32) not null, ageint4 ); psql => revoke all on test from tester; CHANGE psql => select * from test; ERROR: test: Permission denied. Why would this be so? I only revoked the user tester. So, I add myself: Additionally, if I have a trigger on a table which sets the updated_date and updated_by on any update, and I have a view with a rule which passes any update to the table (thus calling the trigger), I get a permission denied. I am supposing that the user updating the view does not have privileges to the trigger? How would I grant them privileges to the trigger? Thank you for your assistance. --Brian
Re: [SQL] Permissions Broken in 7.0.x?
Tom, Thank you for the reply. I have found another issue that I am curious about: I have a table with foreign key constraint to itself (a parent_id field). I have a view to the table with permissions for a user to insert into the view (of course, a rule exists to insert into the real table). The problem is that it fails because of the constraint! Any way around this? Or, am I sort of stuck granting all of these privileges to the table so that triggers, constraints, etc. will work? Thanks, Brian > Brian Powell <[EMAIL PROTECTED]> writes: >> % psql bobdb >> psql => create table test ( >> name char(32) not null, >> ageint4 >> ); >> psql => revoke all on test from tester; >> CHANGE >> psql => select * from test; >> ERROR: test: Permission denied. > > Yeah, that's a bug --- confusion about the default permissions set. > After doing an explicit GRANT or REVOKE on a table you own, you need to > grant permissions to yourself again; the default assumption that you > have all permissions gets lost :-(. This is fixed for 7.1. > >> Additionally, if I have a trigger on a table which sets the updated_date and >> updated_by on any update, and I have a view with a rule which passes any >> update to the table (thus calling the trigger), I get a permission denied. >> I am supposing that the user updating the view does not have privileges to >> the trigger? How would I grant them privileges to the trigger? > > Don't think there's a way to do that right now. There's been talk of > making triggers run "setuid" to the creating user, but it's not been > done AFAIR. --Brian
[SQL] I get an error with Foreign Keys
I have around 40 tables defined, many relying on data in others so I have foreign key constraints. However, whenever I try to delete from any table, I get: ERROR: SPI_execp() failed in RI_FKey_cascade_del() What generates this? I need assistance in knowing where to begin. I checked again, and I could not find any circular foreign key constraints... Any help on where to search is appreciated. Thanks, --Brian
[SQL] 7.1.1 Lock Problems on Views
Greetings, I am investigating whether our application will run on 7.1.1 (from 7.0.3), and our client software only accesses the database through views for security and convenience. In our client app, we lock our table before doing something to it. Fortunately, in 7.0.3, a view looked like a table and we could perform: lock v_whatever in share row exclusive mode; However, in 7.1.1, it comes back and tells me that: ERROR: LOCK TABLE: v_whatever is not a table The HUGE advantage in 7.0.3 was that we could present our db through views and the client didn't have to know. I tried to put the lock statements into the rule of each view when the rule does the actual work, such as: create rule v_whatever_insert as on insert to v_whatever do instead ( lock whatever in share row exclusive mode; insert into whatever (blah) values (new.blah); ); However, the view won't be created, it claims there is a syntax error. So, with 7.1.1, how can I create views and lock the data when modifying the table? Thanks, --Brian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 7.1.1 Lock Problems on Views
On Thursday, May 31, 2001, at 10:42 AM, Tom Lane wrote: > Why don't you redesign the app to not use table-level locks? > An MVCC-aware app should have little or no need for table-level > locking. > Thanks, I'll read up on MVCC in the docs. While digging around, I came across the MVCC and it was the first I have heard of it (I haven't been reading up on the new features of 7.1)... --Brian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] 7.1 LOCK TABLE changes
Greetings, I am investigating whether our application will run on 7.1.1 (from 7.0.3), and our client software only accesses the database through views for security and convenience. In our client app, we lock our table before doing something to it. Fortunately, in 7.0.3, a view looked like a table and we could perform: lock v_whatever in share row exclusive mode; However, in 7.1.1, it comes back and tells me that: ERROR: LOCK TABLE: v_whatever is not a table The HUGE advantage in 7.0.3 was that we could present our db through views and the client didn't have to know. So, in 7.1.1, do I need to move the LOCK TABLE statements from the client software and into the rules of the views? Thanks, --Brian ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Problems using UPDATE and SUM
I am having a SUM problem also. Here is what I am trying to do. There are two tables: cust_rfq1_2 and inventory1 cust_rfq1_2 has four columns: rfq_id(int4),qty(int4),part_id (varchar 30),rev (varchar 2) inventory1 has three columns: part_id (varchar 30),rev (varchar 2), qty_instock (int4) Data in the cust_rfq1_2 looks like this: 4, 5,parta,01 4,10,parta,01 4,10,partb,01 Data in the inventory1 looks like this: parta,01,100 partb,01,100 When I use the select cust_rfq1_2.part_id,cust_rfq1_2.rev,SUM(cust_rfq1_2 from cust_rfq1_2 where cust_rfq1_2.part_id=inventory1.part_id and cust_rfq1_2.rev=inventory1.rev and cust_rfq1_2.rfq_id ='4' group by inventory1.part_id,inventory1.rev" I get good results: part_id,rev,sum parta,01,15 partb,01,10 However, when I use this sql statement for update: "update inventory1 set qty_instock=inventory1.qty_instock + cust_rfq1_2.qty where cust_rfq1_2.qty = ANY (select SUM(cust_rfq1_2.qty) from cust_rfq1_2 where cust_rfq1_2.part_id=inventory1.part_id and cust_rfq1_2.rev=inventory1.rev and cust_rfq1_2.rfq_id='4' )" I get this: inventory1 Results After the update part_id,rev,qty parta,01,100 partb,01,110 inventory1 Results before the update part_id,rev,qty parta,01,100 partb,01,100 inventory1 Results Should be After update part_id,rev,qty parta,01,115 partb,01,110 Any suggestions would be appreicated. Tyge Cawthon ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Need some SQL help
I have a table id int statusint create_dt datetime When users log in and out I insert a row into this table throughout the day. I'm trying though to come up with a querie to tell me and ordering of users as they report in in the morning so i'm trying to say select each user and the earliest time the logged in in a give day, then order that by the create_dt column. Having trouble writing the SQL though ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] timestamp
I think I got it but I'm not sure if it's correct. I put the default as CURRENT_TIMESTAMP but I though I had read that was being taken out and only now was supported? Does anyone know? "Brian Ward" <[EMAIL PROTECTED]> wrote in message anphrb$24se$[EMAIL PROTECTED]">news:anphrb$24se$[EMAIL PROTECTED]... > How do I create a time stamp column in a table that default to the current > time > when a row is inserted? > I tried putting in now() as the default but I have something wrong > with the syntax or something I think > Any one have an example of a table creation script that has a timestamp > column? > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] timestamp
How do I create a time stamp column in a table that default to the current time when a row is inserted? I tried putting in now() as the default but I have something wrong with the syntax or something I think Any one have an example of a table creation script that has a timestamp column? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Stored procedure returning row or resultset
If your f1, f2, and f3 functions depend only on their arguments; that is, if you call one of those functions with the same set of arguments, it will return the same answer every time, you can define the functions as "iscachable" as follows: create function f1() ... language ... with (iscachable); That way, using your SELECT statement, f1 would only be computed once. Furthermore, if you call it a while later with the same arguments, it might not be computed at all. Heiko Stoermer wrote: >Hello, > >I'm trying to achieve the following with pl/pgsql in postgres 7.2: > >I need a stored procedure proc() that calculates values for some fields >a = f1() >b = a + f2() >c = b + f3() >and returns the tuple (a,b,c) as a rowtype, > >so that "select proc()" produces a "normal" resultset that can be used in an >application. > > >I know of course that I could write out >SELECT >(f1()) AS a >(f1() + f()) AS b >(f1() + f2() + f3()) AS c ... >in plain SQL and create a view on that, but the problem is that f1(), f2(), >f3() are complex and expensive computations that I want to run only once. In >the example I would call f1() three times, which is inacceptable. > >Is there a way to accomplish this? I have tried around quite a bit, but I >cannot find out the correct syntax . > >Thank you, >Heiko Stoermer > > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL function triggers
I would like to write a function as a set of SQL statements, and then use that function in a trigger. However, since triggers require a return type of opaque, and SQL functions cannot return type opaque, this doesn't look possible. Am I missing something? The SQL that I would expect to do this is below. Incidentally, on an insert, would the trigger fire once, twice, or infinitely? create table test( a integer, b integer ) without oids; create function test_func( test.a%TYPE ) RETURNS ? AS 'update test set b = a where a = $1;' language SQL with (isstrict); create trigger test_trig after update on test for each row execute procedure test_func( a ); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] UPDATE: Apparent referential integrity bug in PL/pgSQL
The workaround I mentioned in the previous message doesn't turn out to work after all. The series of statements seems to require explicitly calling two functions. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Apparent referential integrity bug in PL/pgSQL
I have a function that operates on two tables A and B, such that B has a foreign key on A, as follows: INSERT INTO A (...) several times INSERT INTO B (...) several times, with foreign keys pointing to the new members of A DELETE FROM A (...), possibly including some of the newly added members Even though B's foreign key is defined ON DELETE CASCADE, I get a referential integrity violation when I run this function. If instead, I comment out the DELETE statement, start a transaction block, run the function, run the DELETE statement, and end the transaction, no errors occur. To run those statements with one function call, I need to split the INSERTs and DELETEs into separate functions, and call them separately from a third function. I am using version 7.2.2. Has this been corrected in the beta versions or can someone confirm this for me? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] functions that return a dataset or set of rows
How do I create a function that returns a set of row; I can't seem to find the datatype that the return set should be declared as. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How To Recover Deleted Data
Do you have a backup of the data? Brian Knox Senior Systems Programmer Web Infrastructure and Security x51613 [EMAIL PROTECTED] On Thu, 29 May 2003, Abdul Wahab Dahalan wrote: > I've accidentally deleted the data in my table. So How to recover it back. > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] "record" datatype - plpgsql
Given a variable of the "record" data type in pl/pgsql, is it possible to get the names of the columns ( attributes ) of that record? eg, given record "NEW" for table "foo", is there a way to get information concerning the columns that make up that record? Brian Knox ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "record" datatype - plpgsql
That's not what I was asking. I know I can access the values of each column using NEW.columnname. What I was asking is if there's anyway to use the NEW record to get a list of the columnnames in it without knowing them beforehand. Brian Knox On Thu, 29 May 2003, George Weaver wrote: > Hi Brian; > > Assuming "NEW" has been declared as foo%rowtype, you can access the > columns thus > > NEW.xxx where xxx is the column name > > HTH. > George > > - Original Message - > From: "Brian Knox" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, May 29, 2003 11:11 AM > Subject: [SQL] "record" datatype - plpgsql > > > > Given a variable of the "record" data type in pl/pgsql, is it possible to > > get the names of the columns ( attributes ) of that record? > > > > eg, given record "NEW" for table "foo", is there a way to get information > > concerning the columns that make up that record? > > > > Brian Knox > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] DISTINCT ON troubles
( sorry if this is a repeat, my mail server is being wonky today ) I'm looking for a way, within SQL, given a starting date and an ending date, to get back the number of months between the start and end date. If I "SELECT end_date - start_date", I get back an interval in days; I need months. Thanks for any suggestions, Brian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Date Foo.
Sorry, Not looking for a way to extract a month from a timestamp. I'm
looking for a way to convert an interval from days to months. I'm not
sure after digging into it that there is a way to handle it in SQL, as
the interval that results from subtracting one timestamp from another is
not away of what months the interval spans, so there'd be no proper way
to take month lengths into account.
I gave up on the sql and used Date::Calc from Perl. Thanks for your
answer anyway.
scott.marlowe wrote:
On Thu, 19 Feb 2004, Brian Knox wrote:
( sorry if this is a repeat, my mail server is being wonky today )
I'm looking for a way, within SQL, given a starting date and an ending
date, to get back the number of months between the start and end date.
If I "SELECT end_date - start_date", I get back an interval in days; I
need months.
Maybe date_part?
select date_part('month','2004-08-02 12:00:00'::timestamp) -
date_part('month','2004-05-01 12:00:00'::timestamp);
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Convert INT to INTERVAL?
Is it possible within pl/pgsql, to convert an integer to an interval in months? IE, if num_months is an INT set to 48, can it be converted to an interval? Thanks, Brian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] timestamp SQL question
I have a table like: events visitor_uid varchar(32) event_type varchar(32) event_type timestamp I would like to select events from the table that are older than 30 days from right now. How would I do that in one query? Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL]
---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: FW: [SQL] Table Valued Parameters
2009/10/23 Andrew Hall : > Hi Pavel, > > many thanks for the very prompt reply. > > I was under the impression that a refcursor was a pointer to a dataset > already resident on the database, and were used to return a reference to a > dataset resident in the database to a client application. > > What I am trying to do is build a table valued variable in a client > application then submit it to a stored procedure resident on the database, > and have that stored procedure manipulate it as though it were a table [be > it inserting, updating or deleting based upon its contents]. > > Is this possible? > > I apologise for not making my question more clear. Is the following too simplistic (maybe I have not understood your question either, but it seems that postgresql makes it so simple, that "problems" you had to solve in ORACLKE, aren't a "problem" in postgresql.) create type ty_app_user as ( aur_id integer, ... etc ); create or replace function prAddUsers ( p_in_users tty_app_user ) returns void as $$ declare begin insert into users (aur_id ... etc ) values (p_in_users.aur_id, etc...); end; $$ language plpgsql; > Thanks, > > Andrew. > >> Date: Fri, 23 Oct 2009 20:10:48 +0200 >> Subject: Re: [SQL] Table Valued Parameters >> From: [email protected] >> To: [email protected] >> CC: [email protected] >> >> Hello >> >> 2009/10/23 Andrew Hall : >> > Hi, >> > >> > I was wondering whether anybody would be able to advise me on how (if it >> > is >> > possible) to port some functionality from Oracle? >> > >> > This is just an example - in Oracle, I am able to do the following >> > >> >> Use refcursor, please. >> >> http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html >> >> Regards >> Pavel Stehule >> >> > -- >> > -- Create a data type which replicates the data structure of a single >> > user >> > in my application. >> > -- I know that this can be done using PostgreSQL. >> > -- >> > >> > CREATE TYPE TY_APP_USER AS OBJECT >> > ( >> > aur_id INT >> > , aur_username VARCHAR2(30 CHAR) >> > , aur_is_account_enabled VARCHAR2(1 CHAR) >> > , aur_created_date DATE >> > , aur_updated_date TIMESTAMP >> > ) >> > / >> > >> > -- >> > -- Create a data type which can store many instances of a single >> > 'TY_APP_USER' >> > -- [essentially this is a table valued data type]. An instance of this >> > data >> > type can be >> > -- created and populated by the client application [a java based one in >> > my >> > case]. >> > -- >> > -- I can't find any reference to something >> > -- similar to this using postgreSQL. >> > -- >> > >> > CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USER >> > / >> > >> > -- >> > -- Next define a stored procedure which can accept an instance of a >> > TTY_APP_USER data >> > -- type, and treat that instance as a table, for example ... >> > -- >> > >> > CREATE OR REPLACE PROCEDURE prAddUsers >> > ( >> > p_in_users IN tty_app_user >> > ) >> > IS >> > BEGIN >> > >> > INSERT >> > INTO >> > users >> > ( >> > aur_id >> > , aur_username >> > , aur_is_account_enabled >> > , aur_created_by >> > , aur_created_date >> > ) >> > SELECT >> > aur_id >> > , aur_username >> > , aur_is_account_enabled >> > , aur_created_by >> > , aur_created_date >> > FROM >> > TABLE >> > ( >> > CAST >> > ( >> > p_in_users AS tty_app_user >> > ) >> > ); >> > >> > END prUpdateUsers; >> > >> > My motivation for doing this is to reduce network round trips, instead >> > of >> > having 1 call per record to be sent to the db, I can have 1 call passing >> > all >> > values which I wish to store in the database. >> > >> > Sending multiple records to the database as a result of a single form >> > submission is a requirement that arises frequently [the example is just >> > intended to demonstrate the principle!], and I would be grateful if >> > anybody >> > could help me to arrive at an optimal solution. >> > >> > Cheers, >> > >> > Andrew. >> > >> > >> > >> > >> > >> > >> > >> > Download Messenger onto your mobile for free. Learn more. >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > Chat to your friends for free on selected mobiles. Learn more. > > Chat to your friends for free on selected mobiles. Learn more. -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Valued Parameters
>> > 'TY_APP_USER' >> >> > -- [essentially this is a table valued data type]. An instance of >> >> > this >> >> > data >> >> > type can be >> >> > -- created and populated by the client application [a java based one >> >> > in >> >> > my >> >> > case]. >> >> > -- >> >> > -- I can't find any reference to something >> >> > -- similar to this using postgreSQL. >> >> > -- >> >> > >> >> > CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USER >> >> > / >> >> > >> >> > -- >> >> > -- Next define a stored procedure which can accept an instance of a >> >> > TTY_APP_USER data >> >> > -- type, and treat that instance as a table, for example ... >> >> > -- >> >> > >> >> > CREATE OR REPLACE PROCEDURE prAddUsers >> >> > ( >> >> > p_in_users IN tty_app_user >> >> > ) >> >> > IS >> >> > BEGIN >> >> > >> >> > INSERT >> >> > INTO >> >> > users >> >> > ( >> >> > aur_id >> >> > , aur_username >> >> > , aur_is_account_enabled >> >> > , aur_created_by >> >> > , aur_created_date >> >> > ) >> >> > SELECT >> >> > aur_id >> >> > , aur_username >> >> > , aur_is_account_enabled >> >> > , aur_created_by >> >> > , aur_created_date >> >> > FROM >> >> > TABLE >> >> > ( >> >> > CAST >> >> > ( >> >> > p_in_users AS tty_app_user >> >> > ) >> >> > ); >> >> > >> >> > END prUpdateUsers; >> >> > >> >> > My motivation for doing this is to reduce network round trips, >> >> > instead >> >> > of >> >> > having 1 call per record to be sent to the db, I can have 1 call >> >> > passing >> >> > all >> >> > values which I wish to store in the database. >> >> > >> >> > Sending multiple records to the database as a result of a single form >> >> > submission is a requirement that arises frequently [the example is >> >> > just >> >> > intended to demonstrate the principle!], and I would be grateful if >> >> > anybody >> >> > could help me to arrive at an optimal solution. >> >> > >> >> > Cheers, >> >> > >> >> > Andrew. >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > Download Messenger onto your mobile for free. Learn more. >> >> >> >> -- >> >> Sent via pgsql-sql mailing list ([email protected]) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> > >> > >> > Chat to your friends for free on selected mobiles. Learn more. >> > >> > Chat to your friends for free on selected mobiles. Learn more. >> >> >> >> -- >> Brian Modra Land line: +27 23 5411 462 >> Mobile: +27 79 69 77 082 >> 5 Jan Louw Str, Prince Albert, 6930 >> Postal: P.O. Box 2, Prince Albert 6930 >> South Africa >> http://www.zwartberg.com/ >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > Download Messenger onto your mobile for free. Learn more. -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function Syntax Help
2009/10/30 Plugge, Joe R. > I am trying to create a function that will grind through a cdr table and > populate another table. I am trying to load the function and am getting the > following error: > > > > ERROR: function result type must be specified > > > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > AS $$ DECLARE > > you need to tell it the return type. If there is none, "returns void" e.g. CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) returns void AS $$ > mycount integer; > > BEGIN > > WHILE mystart < mystop + INTERVAL '1 day' LOOP > > SELECT INTO mycount count(*) FROM log_calls WHERE startdate < mystart > and enddate > mystop; > > INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES > (mystart,mycount); > > mystart := mystart + INTERVAL '1 minute'; > > mystop := mystop + INTERVAL '1 minute'; > > END LOOP; > > END; > > $$ LANGUAGE 'plpgsql' STABLE; > > > > > > > > > > > > [image: image001]*Joe R. Plugge* > > *Database Administrator, West Interactive Corporation* > > *11650 Miracle Hills Drive, Omaha NE 68154* > > *402-716-0349 | Cell 402-517-2710 | [email protected]** * > > > > *This electronic message transmission, including any attachments, contains > information from West Corporation which may be confidential or privileged. > The information is intended to be for the use of the individual or entity > named above. If you are not the intended recipient, be aware that any > disclosure, copying, distribution or use of the contents of this information > is prohibited. * > > * * > > *If you have received this electronic transmission in error, please notify > the sender immediately by a "reply to sender only" message and destroy all > electronic and hard copies of the communication, including attachments.* > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/
Re: [SQL] Function Syntax Help
2009/10/30 Plugge, Joe R. > Thanks Brian, I changed it to this: > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > RETURNS VOID AS $$ > > DECLARE > > mycount integer; > > BEGIN > > WHILE mystart < mystop + INTERVAL '1 day' LOOP > > SELECT INTO mycount count(*) FROM log_calls WHERE startdate < mystart > and enddate > mystop; > > INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES > (mystart,mycount); > > mystart := mystart + INTERVAL '1 minute'; > > mystop := mystop + INTERVAL '1 minute'; > > END LOOP; > > END; > > $$ LANGUAGE 'plpgsql' STABLE; > > > > But now am getting a different error: > > > > [postg...@linux1559 ~]$ cat gen_simultaneous_calls.sql | psql holly > > ERROR: "$1" is declared CONSTANT > > CONTEXT: compilation of PL/pgSQL function "gen_simultaneous_calls" near > line 7 > > mystart and mystop are constants... you could declare variables and copy those into them, and the modify the new variables... > > > > > > > *From:* [email protected] [mailto:[email protected]] *On Behalf > Of *Brian Modra > *Sent:* Friday, October 30, 2009 2:29 PM > *To:* Plugge, Joe R. > *Cc:* [email protected] > *Subject:* Re: [SQL] Function Syntax Help > > > > 2009/10/30 Plugge, Joe R. > > I am trying to create a function that will grind through a cdr table and > populate another table. I am trying to load the function and am getting the > following error: > > > > ERROR: function result type must be specified > > > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > AS $$ DECLARE > > > you need to tell it the return type. If there is none, "returns void" > > e.g. > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop > timestamp) returns void AS $$ > > mycount integer; > > BEGIN > > WHILE mystart < mystop + INTERVAL '1 day' LOOP > > SELECT INTO mycount count(*) FROM log_calls WHERE startdate < mystart > and enddate > mystop; > > INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES > (mystart,mycount); > > mystart := mystart + INTERVAL '1 minute'; > > mystop := mystop + INTERVAL '1 minute'; > > END LOOP; > > END; > > $$ LANGUAGE 'plpgsql' STABLE; > > > > > > > > > > > > [image: image001]*Joe R. Plugge* > > *Database Administrator, West Interactive Corporation* > > *11650 Miracle Hills Drive, Omaha NE 68154* > > *402-716-0349 | Cell 402-517-2710 | [email protected]** * > > > > *This electronic message transmission, including any attachments, contains > information from West Corporation which may be confidential or privileged. > The information is intended to be for the use of the individual or entity > named above. If you are not the intended recipient, be aware that any > disclosure, copying, distribution or use of the contents of this information > is prohibited. * > > * * > > *If you have received this electronic transmission in error, please notify > the sender immediately by a "reply to sender only" message and destroy all > electronic and hard copies of the communication, including attachments.* > > > > > -- > Brian Modra Land line: +27 23 5411 462 > Mobile: +27 79 69 77 082 > 5 Jan Louw Str, Prince Albert, 6930 > Postal: P.O. Box 2, Prince Albert 6930 > South Africa > http://www.zwartberg.com/ > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/
[SQL] loading a file into a field
I am looking to play around with the postgres XML functions, but can't seem to find a way to load my data. I have a number of XML files: file1.xml, file2.xml, etc All files follow the same xml format (DTD?) I would like to create the following table: CREATE TABLE configs ( filenamevarchar(80) PRIMARY KEY, config xml ); and load each file into the xml field, but can't seem to find a way. (I think I need something similiar to lo_import, but have not found anything) Any help is appreciated. Thanks -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
On 19/05/2010, Tim Landscheidt wrote: > Kenneth Marshall wrote: > >> It works using 'now' and I assume that since curtime is >> of type DATE that the assignment casts the return automatically >> to type DATE. Thank you for the ideas. >> [...] > > What's wrong with Pavel's correct and to-the-point answer? No need actually to cast... just use current_date without the quotes. Its not a string. > > Tim > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] running scripts like oracle sqlplus
Have you tried nextval & currval? http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html Something like this: begin insert into user ( user_id, customer_id, create_user, update_user ) values ( nextval(user_seq), nextval(customer_seq), currval(user_seq), currval(user_seq) ); insert into customer ( customer_id, create_user, update_user ) values ( currval(customer_seq), currval(user_seq), currval(user_seq) ); commit; end; On Fri, Jun 18, 2010 at 6:24 PM, Steven Dahlin wrote: > I have been trying to figure out how I can run a pgsql script like I can > run a plsql script with oracle's sqlplus. Here is a sample script file for > what I want to run: > > declare > sysuserid integer := 0; > hwcustid integer := 0; > begin > > select nextval( 'user_seq' ) into sysuserid; > select nextval( 'customer_seq' ) into hwcustid; > > insert into user > ( > user_id, > customer_id, > create_user, > update_user > ) > values > ( > sysuserid, > hwcustid, > sysuserid, > sysuserid > ); > > insert into customer > ( > customer_id, > create_user, > update_user > ) > values > ( > hwcustid, > sysuserid, > sysuserid > ); > > commit; > > end; > > I try to run the script in psql and thru pgadmin and cannot seem to make > them work. I do not want to turn it into a function. I just want it to > execute the block in a fashion similar to Oracle'sqlplus running > @scriptfile.sql. > > Thanks >
[SQL] How To Calculate Table Size Minus Deleted Rows
I'm in the need of a way to determine the disk size of a table that excludes dead tuples. Here is my situation. Our company would like to provide a "rolling storage" solution on a per schema basis. So basically we set a "quota" of the amount of disk space that a schema can occupy. Every night a cron job is run that calculates the overage of each schema's quota and deletes as many rows as necessary from each table to bring the schema back within quota limits. I was trying to use pg_total_relation_size to calculate the amount of space consumed by each table, but I quickly learned that even after deleting/vacuuming, the reported size of the table does not drop. I assume this is due to the nature of how a lazy vacuum works. Is there a way to get the size of the table that excludes the freed (but not released) space from a delete/vacuum run? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Brian Helm Security Confidence Corporation [email protected] 513.388-4500/866.732.2661 Ext 106 www.SecurityConfidence.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Sorting router interfaces
I am trying to sort router interface names.
The problem is that I am doing a text sort and need to do a numerical sort.
I want the interfaces to be in numerical order:
GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
GigabitEthernet1/0/3 | 1/0/3 | {1,0,3}
etc.
What I get instead is the following text ordering:
GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
GigabitEthernet1/0/10 | 1/0/10 | {1,0,10}
GigabitEthernet1/0/11 | 1/0/11 | {1,0,11}
GigabitEthernet1/0/12 | 1/0/12 | {1,0,12}
GigabitEthernet1/0/13 | 1/0/13 | {1,0,13}
GigabitEthernet1/0/14 | 1/0/14 | {1,0,14}
GigabitEthernet1/0/15 | 1/0/15 | {1,0,15}
GigabitEthernet1/0/16 | 1/0/16 | {1,0,16}
GigabitEthernet1/0/17 | 1/0/17 | {1,0,17}
GigabitEthernet1/0/18 | 1/0/18 | {1,0,18}
GigabitEthernet1/0/19 | 1/0/19 | {1,0,19}
GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
GigabitEthernet1/0/20 | 1/0/20 | {1,0,20}
GigabitEthernet1/0/21 | 1/0/21 | {1,0,21}
GigabitEthernet1/0/22 | 1/0/22 | {1,0,22}
GigabitEthernet1/0/23 | 1/0/23 | {1,0,23}
GigabitEthernet1/0/24 | 1/0/24 | {1,0,24}
GigabitEthernet1/0/25 | 1/0/25 | {1,0,25}
GigabitEthernet1/0/26 | 1/0/26 | {1,0,26}
GigabitEthernet1/0/27 | 1/0/27 | {1,0,27}
GigabitEthernet1/0/28 | 1/0/28 | {1,0,28}
GigabitEthernet1/0/29 | 1/0/29 | {1,0,29}
GigabitEthernet1/0/3 | 1/0/3 | {1,0,3}
GigabitEthernet1/0/30 | 1/0/30 | {1,0,30}
GigabitEthernet1/0/31 | 1/0/31 | {1,0,31}
GigabitEthernet1/0/32 | 1/0/32 | {1,0,32}
GigabitEthernet1/0/33 | 1/0/33 | {1,0,33}
FYI: I also have entries like the following:
lc-5/2/0.32769 | 5/2/0.32769 | {5,2,0.32769}
irb.5 | .5 | {.5}
irb.51 | .51 | {.51}
irb.52 | .52 | {.52}
ae6 | 6 | {6}
ae7 | 7 | {7}
lo0.0 | 0.0 | {0.0}
Vlan710 | 710 | {710}
Vlan760 | 760 | {760}
Vlan910 | 910 | {910}
Vlan910 | 910 | {910}
gre | | {""}
tap | | {""}
dsc | | {""}
The above listings are produced with the following:
SELECT
interface,
regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
regexp_split_to_array(regexp_replace(interface,'[A-Za-z
-]+','','g'),E'/') as "sort_col"
FROM all_ports
ORDER BY devicename,sort_col
I have tried to break out the interface number to a separate array
column to sort on and was hoping to cast the array to a float[], but
no luck:
SELECT
interface,
regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
regexp_split_to_array(regexp_replace(interface,'[A-Za-z
-]+','','g'),E'/')::float as "sort_col"
FROM all_ports
psql:-:15: ERROR: cannot cast type text[] to double precision
LINE 5: ...gexp_replace(interface,'[A-Za-z -]+','','g'),E'/')::float as...
Can anyone suggest a better approach or help with this approach?
Thanks
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] select xpath ...
Boris,
Can you send me your final solution?
I am trying to do something similar and I think I am stuck at the namespace.
Thanks
On Mon, Sep 19, 2011 at 11:49 AM, boris wrote:
> On 09/19/2011 10:49 AM, Rob Sargent wrote:
>>
>> Having a name space in the doc requires it's usage in the query.
>
> yeah, I got it... I was using wrong one...
> thanks.
>
>
>>
>>
>> On 09/17/2011 11:48 AM, boris wrote:
>>>
>>> hi all,
>>> I've inserted xml file :
>>>
>>>
>>> >> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
>>>
>>> zz
>>> ..
>>>
>>>
>>> to a table:
>>>
>>> CREATE TABLE "temp".tempxml
>>> (
>>> record xml
>>> )
>>>
>>> I can get it using:
>>> select * from temp.tempxml
>>>
>>>
>>> but, I can't get any values using xpath. ex:
>>>
>>>
>>> select (xpath('/document/title/text()', record ))[1] from temp.tempxml
>>>
>>>
>>> am I doing it right?
>>>
>>> thanks.
>>>
>>>
>>>
>>>
>>
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] help with xpath namespace
I am trying to use xpath to extract some information from a XML document.
(if it matters, It's the router config for a Juniper router)
I believe I am having problems with the namespace.
I am trying to extract the serial-number, but I am not getting anything.
Here is the script I have been testing with:
BEGIN;
CREATE TABLE "xml_test" (
data_xmlxml
);
INSERT INTO xml_test (data_xml) VALUES ('
http://xml.juniper.net/junos/9.6R4/junos-chassis";>
Chassis
JN11
MX960
');
select data_xml from xml_test where data_xml is document;
select (xpath('/chassis-inventory/chassis/serial-number/text()',
data_xml,
ARRAY[ARRAY['junos',
'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
)) from xml_test;
ROLLBACK;
This gives me the following:
BEGIN
CREATE TABLE
INSERT 0 1
data_xml
---
http://xml.juniper.net/junos/9.6R4/junos-chassis";>+
+
Chassis+
JN11 +
MX960+
+
(1 row)
xpath
---
{}
(1 row)
ROLLBACK
Can anyone suggest how I would go about getting the serial-number with xpath?
Thanks
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] help with xpath namespace
Yep, that did it.
Thanks!
2011/9/23 Filip Rembiałkowski :
>
>
> 2011/9/22 Brian Sherwood
>>
>> select (xpath('/chassis-inventory/chassis/serial-number/text()',
>> data_xml,
>> ARRAY[ARRAY['junos',
>> 'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
>> )) from xml_test;
>>
>> Can anyone suggest how I would go about getting the serial-number with
>> xpath?
>>
>
>
> http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
> - see "mydefns".
>
> This will work:
>
> select xpath(
> '/junos:chassis-inventory/junos:chassis/junos:serial-number/text()',
> data_xml,
> ARRAY[ARRAY['junos',
> 'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
> )
> from xml_test;
>
>
>
> cheers, Filip
>
>
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to right justify text in psql?
I am running postgresql 9.2. I am assuming it would be a function of psql to right justify text, but I can't find any way to do this. Is there a way to right justify just one text column? Thanks Brian
[SQL] joining VIEWs
Given a view like: create view view1 as select g.id as UserGroupId, s.uid as UserId, s.time as StartTime from stats s join groups g on g.uid = s.uid and a SELECT like: select a.UserGroupId,b.UserGroupId from view1 a full outer join view1 b on b.UserGroupId = a.UserGroupId WHERE a.StartTime >= '2006-1-1' AND a.StartTime < '2007-1-1' AND b.StartTime >= '2005-1-1' AND b.StartTime < '2006-1-1'; where there are 5695 rows in 2006 and 1 row in 2005, I expected to get a result set of 5695 rows, but instead got only 1 row (the common row in the 2 years). This seems contrary to the definition of "full outer join". Am I missing something? Thanks, Brian Cox Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
[SQL] using sql on v7.4 server to feed stored procedure
I just found out that I posted this to the wrong list before, so if
this ends up as a cross-post, I'm sorry.
I've created some sql scripts to update a database in version 8.x and
now I'm trying to get them to run on servers using v7.4
The following statement works on 8.x servers, but not on 7.4 and I
can't figure out how to get it to operate in the earlier version:
select calc_cum_gpa_mp(marking_period_id::TEXT)
from (select distinct marking_period_id from student_mp_stats) as
sms1;
I get the error:
ERROR: syntax error at or near "("
SQL state: 42601
Context: compile of PL/pgSQL function "calc_cum_gpa_mp" near line 8
Can anyone point me in the right direction? I've searched everywhere
for how to use stored procedures in sql queries, and haven't found how
to use the subquery to feed values to the stored procedure. It almost
looks like I'd need to create another plpgsql function with a loop to
do the same thing.
Brian
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] creating rules on joined views
D'oh sorry for the subscribe message to the list...
Greetings, I am new to PostGreSQL and the advanced capabilities that it
offers. I would like to create a joined view that allows inserts and
updates on two different tables (but, joined twice).
Suppose you have:
create table user {
name varchar(40) not null,
email varchar(80),
address_idint4 not null,
shipping_address_id int4 not null
};
create table addresses {
address_idint4 primary key default nextval('address_seq_id'),
address varchar(80),
city varchar(80)
};
create view v_users as
select u.name
, u.email
, a1.address
, a1.city
, a2.address as ship_address
, a2.city as ship_city
from user u
, addresses a1
, addresses a2
where u.address_id = a1.address_id
and u.shipping_address_id = a2.address_id
Now, how would I create an insert rule on v_users because I have to insert
two addresses first, retrieve their id's and insert those into the user
table. Conversely, an update introduces its own challenges as well. If
someone could show me these pieces, I have pretty much figured out
everything else I need to do with the foreign keys, etc. It seems doable
easily with pgpsql; however, I cannot use that for a rule, correct?
Thanks,
Brian
[SQL] Help with inserts into Views
Greetings,
I am having difficulty creating a way to insert into a view which joins
multiple tables (specifically a view which joins multiple records from a
single table to a record in another table).
Please see the enclosed simple example for how I am currently inserting
(updating, etc.) on views. However, When I get more complex tables with
multiple joins, this method will not work because a plpgsql function can
accept only 16 arguments. I have tried using the NEW variable as the
argument to the function; however, this does not seem to work.
No matter what I do to create an insert trigger on the view, it never seems
to fire the trigger.
Any help and suggestions on how to perform an insert into multiple tables
from a single joined view would be greatly appreciated.
Thank you,
Brian
Example:
drop sequence addr_id_seq;
drop sequence member_id_seq;
drop rule v_member_insert;
drop function member_insert(varchar, varchar, varchar, varchar, varchar,
varchar, varchar);
drop view v_member;
drop table member;
drop table addr;
create sequence addr_id_seq;
create table addr (
addr_idint4 primary key default nextval('addr_id_seq'),
street varchar(40) not null,
city varchar(40) not null,
state varchar(40) not null
);
create sequence member_id_seq;
create table member (
member_idint4 primary key default nextval('addr_id_seq'),
username varchar(40) not null,
address_id int4 not null,
shipping_id int4 not null
);
create view v_member as
select m.member_id, m.username, a.street, a.city, a.state,
s.street as ship_street, s.city as ship_city,
s.state as ship_state
from member m, addr a, addr s
where m.address_id = a.addr_id and m.shipping_id = s.addr_id;
create function member_insert(varchar, varchar, varchar, varchar,
varchar, varchar, varchar) returns text as '
declare
my_address_id integer;
my_shipping_id integer;
my_username ALIAS FOR $1;
my_street ALIAS FOR $2;
my_city ALIAS FOR $3;
my_state ALIAS FOR $4;
my_ship_street ALIAS FOR $5;
my_ship_city ALIAS FOR $6;
my_ship_state ALIAS FOR $7;
begin
my_address_id := nextval(''addr_id_seq'');
insert into addr
(addr_id, street, city, state)
values (my_address_id, my_street, my_city, my_state);
my_shipping_id := nextval(''addr_id_seq'');
insert into addr
(addr_id, street, city, state)
values (my_shipping_id, my_ship_street, my_ship_city,
my_ship_state);
insert into member (username, address_id, shipping_id)
values (my_username, my_address_id, my_shipping_id);
return ''Success'';
end;
' language 'plpgsql';
CREATE RULE v_member_insert AS
ON INSERT TO v_member
DO INSTEAD
SELECT member_insert(new.username, new.street, new.city, new.state,
new.ship_street, new.ship_city, new.ship_state);
-- Should create an error
insert into v_member (username) values ('bob');
-- Should create a record
insert into v_member
(username, street, city, state, ship_street, ship_city, ship_state)
values ('bob', '123 Main', 'Denver', 'CO', '543 Elm', 'Buttland',
'MS');
select * from member;
select * from addr;
select * from v_member;
[SQL] Backup of BLOBS
Greetings, I have a database that will be quite large that must be backed up nightly. I would like to use pg_dump; however, the problem is that we store binary data as well. Is there a way to backup this up without having to write a program to do it? Thanks, Brian
[SQL] Bug in to_char()
Greetings,
Working with PostGreSQL 7.02, I found the following problem:
The AM/PM designator in the to_char function does not work proper for 13:00
and 12:00.
See the following:
test=> select to_char('3-12-2000 14:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 02:00 PM
(1 row)
This is correct.
test=> select to_char('3-12-2000 8:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 08:00 AM
(1 row)
This is correct.
test=> select to_char('3-12-2000 13:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 01:00 AM
(1 row)
NO! This is incorrect
test=> select to_char('3-12-2000 1:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 01:00 AM
(1 row)
This is correctly 1 am.
nicklebys=> select to_char('3-12-2000 12:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 12:00 AM
(1 row)
NO! This is 12:00 pm. 0:00 or 24:00 is 12:00 am.
Any known work arounds or bug fixes planned?
Thanks,
Brian
--
+---+
| Brian Powell, President [EMAIL PROTECTED] |
| Filo Group www.filogroup.com|
| One Broadway, Suite 300AAIM: filogroupbrian |
| Denver, CO 80203 ICQ: 75037370|
| 303.733.3248 office 303.733.7122 fax |
+---+
[SQL] Time Help
Hello all, I have a query result of @ 2 hours 10 mins 6 secs and I would like to change that to 02:10:06. Currently the field is listed as "timespan" This allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec which are all the formats that I will be entering the time formats! How do I convert it into a the format of 02:10:06 Thanks to you all
Re: [SQL] Time Help
Mark, I tried that and had to change it to: SELECT '0:00:00'::timespan + '02:10:06'::timespan; To get any response. the response i got was @ 2 hours 10 mins 6 secs Still in the wrong format If is use : SELECT '0:00:00'::time + '02:10:06'::timespan; It get No such function 'time_timespan' with the specified attributes So i guess what I want to do is convert a timespan into time How would I do that? At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: >I'm not sure at all what you are asking, but I'm thinking you're trying to >convert a "timespan" to a "time". Try adding it to a time like this: >SELECT '0:00:00'::time + '02:10:06'::timespan; > >Mark > >"Brian C. Doyle" wrote: > > > > Hello all, > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would like to > > change that to 02:10:06. Currently the field is listed as "timespan" This > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec > > which are all the formats that I will be entering the time formats! How do > > I convert it into a the format of 02:10:06 > > > > Thanks to you all
Re: [SQL] Time Help
In Conclusion: 7.0.2 by default outputs the time span as 00:00:00 no matter what format you put in. There is no need to convert it! Thanks to Mark for all your help!! At 11:03 AM 8/22/00 -0400, Mark Volpe wrote: >SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs'::timespan; > > ?column? >-- > 02:10:06 > >Mark > > >"Brian C. Doyle" wrote: > > > > Mark, > > > > On your 7.0 box would you do: > > > > SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs':timespan; > > > > For me and see if it will convert it! Need to decide if the upgrade will be > > with it and if it does this then it is > > > > Thanks for your help Mark > > > > At 10:36 AM 8/22/00 -0400, you wrote: > > >I tried it on a box with postgres 6.5.3 and I got the result you did. On > > >postgres 7.0 I get 02:10:06 for both results. You should upgrade to > 7.0 - it > > >is _much_ better. > > > > > >Mark > > > > > >"Brian C. Doyle" wrote: > > > > > > > > Mark, > > > > > > > > I tried that and had to change it to: > > > > > > > > SELECT '0:00:00'::timespan + '02:10:06'::timespan; > > > > > > > > To get any response. the response i got was > > > > > > > > @ 2 hours 10 mins 6 secs > > > > > > > > Still in the wrong format > > > > If is use : > > > > > > > > SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > > It get > > > > > > > > No such function 'time_timespan' with the specified attributes > > > > > > > > So i guess what I want to do is convert a timespan into time > > > > How would I do that? > > > > > > > > At 09:55 AM 8/22/00 -0400, Mark Volpe wrote: > > > > >I'm not sure at all what you are asking, but I'm thinking you're > trying to > > > > >convert a "timespan" to a "time". Try adding it to a time like this: > > > > >SELECT '0:00:00'::time + '02:10:06'::timespan; > > > > > > > > > >Mark > > > > > > > > > >"Brian C. Doyle" wrote: > > > > > > > > > > > > Hello all, > > > > > > > > > > > > I have a query result of @ 2 hours 10 mins 6 secs and I would > like to > > > > > > change that to 02:10:06. Currently the field is listed as > > > "timespan" This > > > > > > allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 > > > min 6 sec > > > > > > which are all the formats that I will be entering the time formats! > > > How do > > > > > > I convert it into a the format of 02:10:06 > > > > > > > > > > > > Thanks to you all
[SQL] Time Results Conversion
Hello all, I have now upgraded to 7.0.2 and am very pleased with it. I do have a question about a result that I am getting. When I sum an interval field will get "1 01:01:01" representing "25 hours 1 minute 1 second" The result that I need is just the sum of the hours minutes and seconds and No days ie roll the day back into the hour field. Is it possible for me to do this and if so How?
Re: [SQL] Week of the Year?
John,
Would you have any clue how to figure out the first saturday of any month -
6 days and the last saturday of that month?
I know that this seems odd but i have to run reports for "Non Standard
Months" and well I am clueless.
At 09:55 PM 8/11/00 -0500, you wrote:
>Try using the function date_part such as:
>
>select date_part('week',now());
>
>"and the days that are in that week" I guess want to answer a question
>such as:
>Given a date, what is first date in that same week, and what is the last
>date in that week. There are a couple of approaches to this. My first was:
>
>select
>to_date(date_part('year',now()),'')+(7*date_part('week',now()));
>
>and the above +6 to the the last day of the week. Another approach for
>this same question is much simplier (if the question is indeed what you
>are asking)
>
>select now()-date_part('dow',now());
>
>This last select gives the Sunday for the current week. To get the
>Saturday, simply:
>
>select now()-date_part('dow',now())+6;
>
>Of course, replace the now() with whatever contains the date or timestamp.
>
>John McKown
>
> > I'm probably staring right at it. (One of the difficulties with RTFMing, is
> > having too many docs!)
> >
> > Is there anything in the API that produces the week of the year, from 1 to
> > 52 or 53 depending on the week of the year, and the days that are in that
> > week?
> >
> > Many thanks.
> >
> > -dlj.
> >
> >
> >
[SQL] Multiple Index's
Hello all, How would I prevent a user from submitting information to a table once they have already done so for that day. I would need them to be able information on future dates as well as have information in the table from past dates from that user. I am looking for something like insert user_id, date, info where user_id and date are not the same... does that make sense? Brian C. Doyle
Re: [SQL] Multiple Index's
See if this help the table has userid | date | helped_customers An employ will enter in their userid, the date and how many customer they helped that day. What I want to do is prevent the employees from enter the data more than once a day At 10:28 AM 9/21/00 -0700, Mitch Vincent wrote: > > Hello all, > > > > How would I prevent a user from submitting information to a table once >they > > have already done so for that day. > >The best you could probably do is to go back and delete undesired recoords >at the end of the day because if it is as you said, they've already put the >information into the database. > > > I would need them to be able > > information on future dates as well as have information in the table from > > past dates from that user. > >Not positive what you mean here but just use a date (or timestamp) column in >the table to indicate when the record was added. > > > I am looking for something like insert user_id, date, info where user_id > > and date are not the same... does that make sense? > >Nope, it doesn't -- at least to me :-) > >How about some table structures and some more information, I'm just not >exactly sure what you'd like to do.. > >-Mitch
Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp
how would you do that with in a query? ie select date , interval(reltime 'table.secs'); when the value in table.secs = 54321 and "secs" is not a part of it? At 11:35 AM 9/21/00 -0700, Jie Liang wrote: >Hi, Webb, > >I am not quit sure what you really want to do, however, I assume that >following maybe >what you mean: > > >db=# select interval(reltime '12313221 secs'); > interval > > 4 mons 22 12:20:21 >(1 row) > > >Webb Sprague wrote: > > > Hi all, > > > > How do I convert from seconds (an integer) to > > timestamp? > > > > I am sure it is easy, but I can't find it in the docs, > > so far. > > > > Thanks > > Webb > > > > __ > > Do You Yahoo!? > > Send instant messages & get email alerts with Yahoo! Messenger. > > http://im.yahoo.com/ > >-- >Jie LIANG > >Internet Products Inc. > >10350 Science Center Drive >Suite 100, San Diego, CA 92121 >Office:(858)320-4873 > >[EMAIL PROTECTED] >www.ipinc.com
[SQL] Table Attribute Help
Hello all, I am trying to find a query to retrive the attributes of a table as in \d tablename but as a select command. Is this possible?
Re: AW: [SQL] Table Attribute Help
That is great thank you. How would I grab the attribute type for an attribute with it so that the out put would look like attname atttype -- -- userid varchar(30) I know that is not correct but is it possible to get that out put At 05:27 PM 10/9/00 +0200, you wrote: >yes it's possible, > >SELECT pg_attribute.attname >FROM pg_class, pg_attribute >WHERE >pg_class.relname = 'xxx' and pg_attribute.attrelid = pg_class.oid > >and pg_attribute.attnum>=1 order by pg_attribute.attnum; > >xxx is your tablename! > > >-Ursprüngliche Nachricht- >Von: Brian C. Doyle [mailto:[EMAIL PROTECTED]] >Gesendet: Montag, 9. Oktober 2000 17:21 >An: [EMAIL PROTECTED] >Betreff: [SQL] Table Attribute Help > > >Hello all, > >I am trying to find a query to retrive the attributes of a table as in \d >tablename but as a select command. Is this possible?
[SQL] if else query help
Hello all, I need to write a query that will pull information from table2 if information in table1 is older then xdate. My laymen example: SELECT table2.date, count(table2.name) as count WHERE table1.startdate > 2 weeks AND table2.submitdate > 2 weeks ; So i Guess my real questions is how do I determine the age of an entry to another table?
Re: [SQL] COUNT
Hello, You will need to do "SELECT count(attribute) FROM table;" or SELECT count(table.attribute);" At 04:58 AM 10/20/00 +, Craig May wrote: >Hi, > >How do I get a row count, like "Select [COUNT] from Table" ?? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au
Re: [SQL] Alternate Database Locations
Never mind... I got it working At 02:40 PM 10/24/00 -0400, Brian C. Doyle wrote: >Hello, > >I am working on seting up alternate database locations for my users. >I have done > >% initlocation /home/userid >% createdb -D /home/userid userid > >and i always get > >ERROR: The database path '/home/httpd/jbbent' is invalid. This may be due >to a character that is not allowed or because the chosen path isn't >permitted for databases > >I have done both of these as the user and as postgres I have had the dir >chmod to 777 and still nothing. what am I doing wrong? >
[SQL] Alternate Database Locations
Hello, I am working on seting up alternate database locations for my users. I have done % initlocation /home/userid % createdb -D /home/userid userid and i always get ERROR: The database path '/home/httpd/jbbent' is invalid. This may be due to a character that is not allowed or because the chosen path isn't permitted for databases I have done both of these as the user and as postgres I have had the dir chmod to 777 and still nothing. what am I doing wrong?
Re: [SQL] Alternate Database Locations
Well I got it working kinda. You have set up an environment for the new directory and export it under the PGSQL super user account PGDATA2=/home/userid export PGDATA2 Then run initlocation PGDATA2 After that you have to restart your postmaster with the data dir as PGDATA2 postmaster -d PGDATA2 Now you can greate a new db in a new location createdb mydb -D PGDATA2 that is it and you have a new location for ALL your database if you use it ... If you do not add -D PGDATA2 for future db it writes to the default location but you may have problems access it. I am still trying to find out how to get multiple postmasters running on different ports at the same time. Does anyone have any clue how to do that? At 12:04 PM 10/25/00 -0700, Roderick A. Anderson wrote: >On Tue, 24 Oct 2000, Brian C. Doyle wrote: > > > Never mind... I got it working > >OK, don't play your cards so close to the vest. What was the problem (and >solution)? > > >TIA, >Rod >-- >Roderick A. Anderson >[EMAIL PROTECTED] Altoplanos Information Systems, Inc. >Voice: 208.765.6149212 S. 11th Street, Suite 5 >FAX: 208.664.5299 Coeur d'Alene, ID 83814
Re: [SQL] Alternate Database Locations
Okay I am still doing something wrong here I set PGDATA2=/home/user1/database export PGDATA2 then I start postmaster postmaster -d PGDATA2 -i -p 5431 & and i get Can't create pid file: /usr/local/pgsql/data/postmaster.pid Is another postmaster (pid: 10686) running? What am I missing... I know it must be simple!!! Thanks for all of your help! At 10:43 PM 10/25/00 -0400, Tom Lane wrote: >"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > > I am still trying to find out how to get multiple postmasters running on > > different ports at the same time. Does anyone have any clue how to do > that? > >Uh, you just do it. Start each postmaster in a different data directory >and with a different port number (-D and -P switches) and you're set. > >Unless you run out of shared memory or some such, in which case some >tweaking of kernel parameters is called for... > > regards, tom lane
Re: [SQL] Alternate Database Locations
Okay... I can now run as many postmasters as I could imagine... KICK BUTT I am still running into a problem with the "alternate database locations" and that is I seem to be able to only set up 1 other alternate location. After getting the first alt location setup Iset PGDATA2= /home/user1/database export it initdb -D /home/user1/database started postmaster -D /home/user1/database initlocation 'PGDATA2' createdb user1 -D 'PGDATA2' it creates but it is putting it in /home/user/database the first alt location i set up!!! I know I am being a pain in the ass but I truly do appreciate all the help At 10:02 AM 10/26/00 -0400, Tom Lane wrote: >"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > > Okay I am still doing something wrong here > > I set PGDATA2=/home/user1/database > > export PGDATA2 > > > then I start postmaster > > postmaster -d PGDATA2 -i -p 5431 & > >I think you are confusing the "alternate database directory" stuff with >having a separate installation (primary directory). To set up a second >postmaster, you first have to initdb its installation directory: > > initdb -D /home/user1/database > >then start the postmaster like so: > > postmaster -D /home/user1/database -p whatever ... > >Instead of -D (note upper case), you can alternatively set env variable >PGDATA for these two programs. > >The stuff in the manual about alternate database directories is to allow >a *single* postmaster to manage databases located somewhere other than >underneath its installation directory. It's got nothing at all to do >with starting additional postmasters. > > regards, tom lane
[SQL] Query Help
What do I have to do a query where information in table1 is not in table2 I am looking for something like Select table1.firstname where table1.firstname is not in table2.firstname and table2.date='yesterday' I tried Select table1.firstname where table1.firstname != table2.firstname and table2.date='yesterday' and that did not work just listed everyone
Re: [SQL] Query Help
Thank you to everyone with their suggestions. Where on the PostgreSQL site would I have found more info on the NOT EXISTS At 11:20 AM 12/27/00 -0500, you wrote: >What do I have to do a query where information in table1 is not in table2 > >I am looking for something like > >Select table1.firstname where table1.firstname is not in table2.firstname >and table2.date='yesterday' > >I tried >Select table1.firstname where table1.firstname != table2.firstname and >table2.date='yesterday' > >and that did not work just listed everyone >
Re: [SQL] Problem with Day of Week
Keith,
Try:
select to_char('now'::timestamp,'Dy');
to_char
-
Mon
(1 row)
--
DAY = full upper case day name (9 chars)
Day = full mixed case day name (9 chars)
day = full lower case day name (9 chars)
DY = abbreviated upper case day name (3 chars)
Dy = abbreviated mixed case day name (3 chars)
dy = abbreviated lower case day name (3 chars)
DDD = day of year (001-366)
DD = day of month (01-31)
D = day of week (1-7; SUN=1)
-
Taken from
http://www.postgresql.org/users-lounge/docs/7.0/user/functions2972.htm
At 12:55 PM 1/29/01 -0500, Keith Perry wrote:
>Greetings,
>
>I notice some talk about date problems and interestingly enough planning
>out an application in which I will need to be able to manipulate dates.
>I notice however that there seems to be a discrepancy with the day or
>week in 7.0.3
>
>---
>
>pmhcc=# select date_part('dow','now'::timestamp);
> date_part
>---
> 1
>(1 row)
>
>pmhcc=# select to_char('now'::timestamp,'D');
> to_char
>-
> 2
>(1 row)
>
>pmhcc=# select version();
>version
>-
> PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
>(1 row)
>
>pmhcc=# select now();
> now
>
> 2001-01-29 12:57:46-05
>(1 row)
>
>---
>
>Now as far as I know, Sunday is supposed to 1, so I would think that
>date_part is doing something funky unless I am mis-understanding
>something. I was hoping that someone might be able to shead some light
>on this. Thanks.
>
>Keith C. Perry
>VCSN, Inc.
>http://vcsn.com
[SQL] Complex Query Help
Hello all, I have a table of work shifts. It has Table "teams" Attribute | Type | Modifier +---+-- team_id| varchar(50) | team_name | varchar(100) | location | varchar(100) | department | varchar(100) | shift | varchar(100) | start_time | numeric(30,6) | end_time | numeric(30,6) | mon| boolean | tue| boolean | wen| boolean | thu| boolean | fri| boolean | sat| boolean | sun| boolean | start_day | char(3) | end_day| char(3) | What I need to be able to do is select team_name where team works between a time span ie teams that work between 730 and 1600 how would I do this What type of field should i sue for start_time and end_time? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Cursor returned from procedure ignores setFetchSize() on CallableStatement
Hello all -
I need to return large result sets and therefore need a cursor with a small
fetch size (to avoid caching the entire query at once). However, it appears
that when a cursor is returned from a callable statement setFetchSize is
ignored.
I set up a query that crashes with outofMemoryError if entire query is
cached. I am able to sucsessfully use a Statement and setFetchSize() to
avoid the crash. However, when I put the code in a procedure, the
outofMEmoryError throws before I even try to access the result set,
indicating that the entire query is caching. This is true with or without
calling setFetchSize on the ResultSet in addition to the CallableStatement.
Is this a bug or am I doing something wrong? Any comments greatly
appreciated. Thanks
Client
Code
---
cn.setAutoCommit(false);
CallableStatement proc = cn.prepareCall("{ ? = call reffunc2('cursor1') }");
proc.setFetchSize(100);
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
rds = (ResultSet) proc.getObject(1);
rds.setFetchSize(100);
while (rds.next()){
...
}
Function
-
CREATE OR REPLACE FUNCTION public.reffunc2(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT ..;
RETURN $1;
END;
' LANGUAGE 'plpgsql' VOLATILE;
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Outputting the Tables of a database
Hello all, I am trying to out put the tables of a database. I have psql -d mydb -c "\d" This works but I am looking at turning on the html out put which it will not do. If I am able to get just the table titles that would be most ideal.
[SQL] Outputting the Tables of a database
Hello all, What would the query line be to mimic mydb=>\d tablename ? I have been able to run a query to mimic #psql -l and to mimic mydb=>\d thanks for the help Brian
