[SQL] Question regarding Function & Return table
All, I am a beginner to learn Pl/ SQL. How can I return either " Return Pl/SQL table" or "Return VARCHAR2" simultaneously? The result is dependable on Count(Last_name) condition. Thanks, The below is incomplete because I cannot understand the above question. === create or replace function New_Rental( P_last_name S_member.LAST_NAME%TYPE, P_member_id S_member.Member_id%TYPE, P_first_name S_member.FIRST_NAME%TYPE, P_phone S_member.Phone%TYPE, P_VALID_DATE In OUT Date) Return VARCHAR2 Is V_msg :='Expected due date is'||P_VALID_DATE; Begin If Count(Last_name)=1 then Select VALID_DATE INTO P_VALID_DATE from S_member Where Last_Name:= P_Last_name; return 'Expected due date is'||P_VALID_DATE; ElSIF Count(Last_name)> 1 then Select VALID_DATE INTO P_VALID_DATE from S_member Where Last_name:=P_last_name And Member_id:= P_member_id; return 'Expected due date is'||P_VALID_DATE||'Person's record as follows:'||P_last_name,P_first_name, P_phone, P_member_id ; Else Count(Last_name)=0 return 'This person needs to be registered as a member' End If; End New_Rental; == ---(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
Re: [SQL] How to find primary keys by querying system tables
> > Is there a way of testing for membership in an int2vector-field? For > example: > select column, "prim.key" from ... where pg_attribute.attnum in > pg_index.indkey > ?? > I think so. Refer to the array section of the documentation. Extract: To search for a value in an array, you must check each value of the array. This can be done by hand (if you know the size of the array): SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 1 OR pay_by_quarter[2] = 1 OR pay_by_quarter[3] = 1 OR pay_by_quarter[4] = 1; However, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is unknown. Although it is not part of the primary PostgreSQL distribution, in the contributions directory, there is an extension to PostgreSQL that defines new functions and operators for iterating over array values. Using this, the above query could be: SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 1; To search the entire array (not just specified columns), you could use: SELECT * FROM sal_emp WHERE pay_by_quarter *= 1; In addition, you could find rows where the array had all values equal to 10 000 with: SELECT * FROM sal_emp WHERE pay_by_quarter **= 1; To install this optional module, look in the contrib/array directory of the PostgreSQL source distribution. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] autoincrement
How can I reproduce in PostgreSQL type structure from MySQL: INT NOT NULL PRIMARY KEY AUTO_INCREMENT ? To be more exact -- in two words: "how can I create autoincrement field?" Thanks a lot. -- green [http://www.extrasy.net] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] URGENT: restoring a database
On Friday, October 26, 2001, at 11:22 AM, Oleg Lebedev wrote: > Hi, > I think I got a problem here. > I tried to restore my database from dump.bac file, which was created > with > pg_dumpall -o > dump.bac > This is what I did: >> createdb replica >> psql -d replica -f dump.bac > Sounds like you didn't read up on pg_dump to closely. What you probaly should have doe was not use pg_dumpall, but pg_dump -o -F t livedb > livedb.dump.tar The use pg_restore My distribution doesnt have pg_dumpall, but I imagine that unlike pg_dump where it does \connect - someuser at the top. It does \connect origdb someuser So you probably want to edit a copy of the dump file, remove the db nmae from the connect statement, then drop the old db then You should be able to execute that section of the dump pertaining to the db you want to restore on the replacement original, and the new database. EG: Your edited snippet of the dump (Checcking all \connect statements to ensure they are eiter removed, or refer to currently connected database (-) is in dbdump.sql psql -U username > CREATE DATABASE live; > \connect live > \i dbdump.sql > CREATE DATABASE replica; > \connect replica > \i dbdump.sql > \q > Notice that I have two different databases stored in this file. > This is what I got: > > You are now connected to database template1. > DELETE 3 > psql:db_10_22_01.bac:7: ERROR: CREATE USER: permission denied > psql:db_10_22_01.bac:8: ERROR: CREATE USER: permission denied > psql:db_10_22_01.bac:9: ERROR: CREATE USER: permission denied > psql:db_10_22_01.bac:11: ERROR: pg_aclcheck: invalid user id 503 > You are now connected to database template1 as user postgres. > psql:db_10_22_01.bac:18: ERROR: CREATE DATABASE: database "webspectest" > > already exists > You are now connected to database webspectest as user postgres. > CREATE > DROP > You are now connected as new user postgres. > psql:db_10_22_01.bac:48: NOTICE: CREATE TABLE/PRIMARY KEY will create > implicit index 'activitytype_pkey' for table 'activitytype' > psql:db_10_22_01.bac:48: ERROR: Relation 'activitytype' already exists > psql:db_10_22_01.bac:65: NOTICE: CREATE TABLE/PRIMARY KEY will create > implicit index 'dcr_pkey' for table 'dcr' > > Obviously, no database was created. Moreover, I can not access my > neither of my existing databases anymore. > When I try: >> psql webspectest > I get an error: > psql: FATAL 1: user "olebedev" does not exist > > At this point I am completely stuck. > Please help. > thanks, > > Oleg > > > ---(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 > > -- Best Regards David Stanaway Technology Manager Australia's Premier Internet Broadcasters Phone: +612 9357 1699 Fax: +612 9357 1169 Web: http://www.netventures.com.au Support: [EMAIL PROTECTED] The Inspire Foundation is proudly supported by Net Ventures through the provision of streaming solutions for it's national centres. The Inspire Foundation is an Internet-based foundation that inspires young people to help themselves, get involved and get online. Please visit Inspire at http://www.inspire.org.au ---(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
Re: [SQL] autoincrement
Use SERIAL instead of AUTO_INCREMENT. > How can I reproduce in PostgreSQL type structure from MySQL: > INT NOT NULL PRIMARY KEY AUTO_INCREMENT > > ? > > To be more exact -- in two words: "how can I create autoincrement > field?" > > Thanks a lot. > > -- > green > [http://www.extrasy.net] > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] autoincrement
SERIAL type (look it up in the docs). Regards, Aasmund PS. I may not be good at counting, but I have counted your two words 3 times and I still get 6. On Thu, 25 Oct 2001 23:09:32 +0300, Alexey Prohorenko <[EMAIL PROTECTED]> wrote: > How can I reproduce in PostgreSQL type structure from MySQL: > INT NOT NULL PRIMARY KEY AUTO_INCREMENT > > ? > > To be more exact -- in two words: "how can I create autoincrement > field?" > > Thanks a lot. > > -- > green > [http://www.extrasy.net] > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] GUID in postgres
I wrote: > Globally Unique IDentifier, probably. Just hash a 128 bit random number > with the current date. Horst writes: > That gives you no gurantee it will be unique. There is no such guarantee. The probability of a collision due to errors and bugs using a "deterministic" system is sure to be at least as large as the the probability of a chance collision using large random numbers (_random_, not pseudorandom). Stick machine, table, and database ID's in there as well if it makes you more comfortable, but even without them the risk of a collision is down there with the risk of cosmic ray induced errors. _Nothing_, however, can make it zero. > - All tables in need of a global ID _within_ a database inherit a globid > table which contains nothing but an ID of type serial. - When we need > cross-database unique IDs within the same system, the globid table > contains a database identifier as well (like the OID of the pg_database > entry for the database). And that's fine, but the GUID system uses the word "global" in a much more grandiose sense. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin ---(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] Connecting to different DataBase In PlPgsql Function
hello all, How can we connect to different database using plpgsql function? Can we? Thankx in advance. == A beer delayed is a beer denied. == Regards, Bhuvaneswar. ---(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
Re: [HACKERS] [SQL] Index of a table is not used (in any case)
Hello Tom! Tom Lane wrote: > > Reiner Dassing <[EMAIL PROTECTED]> writes: > > explain select * from wetter order by epoche desc; > > NOTICE: QUERY PLAN: > > > Index Scan Backward using wetter_epoche_idx on wetter > > (cost=0.00..3216018.59 rows=2034 width=16) > > > explain select * from wetter where epoche between '1970-01-01' and > > '1980-01-01' order by epoche asc; > > NOTICE: QUERY PLAN: > > > Sort (cost=480705.74..480705.74 rows=203400 width=16) > > -> Seq Scan on wetter (cost=0.00..454852.00 rows=203400 width=16) > > It's hard to believe that you've done a VACUUM ANALYZE on this table, > since you are getting a selectivity estimate of exactly 0.01, which > just happens to be the default selectivity estimate for range queries. > How many rows are there really in this date range? > Well, I did not claim that i made a VACUUM ANALYZE, I just set up a new table for testing purposes doing just INSERTs. After VACUUM ANALYSE the results look like: explain select * from wetter where epoche between '1970-01-01' and test_wetter-# '1980-01-01' order by epoche asc; NOTICE: QUERY PLAN: Index Scan using wetter_epoche_idx on wetter (cost=0.00..3313780.74 rows=20319660 width=16) EXPLAIN Now, the INDEX Scan is used and therefore, the query is very fast, as expected. For me, as a user not being involved in all the intrinsics of PostgreSQL, the question was "Why is this SELECT so slow?" (this question is asked a lot of times in this Mail lists) Now, I would like to say thank you! You have explained me and hopefully many more users what is going on behind the scene. > Anyway, the reason the planner is picking a seqscan+sort is that it > thinks that will be faster than an indexscan. It's not necessarily > wrong. Have you compared the explain output and actual timings both > ways? (Use "set enable_seqscan to off" to force it to pick an indexscan > for testing purposes.) > > regards, tom lane -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(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
Re: [SQL] Lock full database
> > I want lock full tables,how to write SQL command? > thanks > BEGIN ; LOCK TABLE ; /* done, locked until "commit;" */ Refer to the SQL-LOCK-command for further information. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] GUID in postgres
Horst, > What we are using is the following: > - All tables in need of a global ID _within_ a database inherit a > globid > table which contains nothing but an ID of type serial. > - When we need cross-database unique IDs within the same system, the > globid > table contains a database identifier as well (like the OID of the > pg_database > entry for the database). Well, I think you've just answered your own question. Build the above. In more specific: 1. PostgreSQL does not, as a design decision, support inter-database queries. So an inter-database ID is not particularly useful. 2. If you needed an id to be unique between servers for some reason, simply make it a two-column ID: one column for the sequence (see below) and one for the server name/ID 3. Sequences are guarenteed unique within a database up to the limits of INT4 (2.4 billion). Read up on them in the postgreSQL docs. Also see my posts on pgsql-sql for the last week regarding primary keys. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] [Q] External join
Hi, I wish to have the confirmation that external join is not possible with postgresql (v 7.0). In advance, thanks mb ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] [LONGINT] Problem
Hi, Guys, I am new to PostgreSQL -- (I am migrating from MySQL), so I have some, may be "stupid", but still interesting questions. Interesting for me. :-) In MySQL there was type called INT UNSIGNED, where I used to keep values from 1 to 4294967295 without any problems. 1 is '0001' in binary, and 4294967295 is ''. I also was able to do f.e. 'SELECT xxx WHERE (xxx & 2147483648)' and get everything I want. 2147483648 is '1000' in binary. Nevertheless, with PostgreSQL I have troubles. I didn't find any type which will help me to do everything above mentioned. May be someone of you was in the same situation, or has enough knowledge to help me with that? I'll be very gladfull for any answer, Looking forward them. Thanks. P.S. If possible -- do CC: [EMAIL PROTECTED] when answering, cause I am not sure does Majordomo subscribe me or not. :-) -- green [http://www.extrasy.net] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [LONGINT] Problem
On Thu, 25 Oct 2001, Alexey Prohorenko wrote: > I am new to PostgreSQL -- (I am migrating from MySQL), so I have > some, may be "stupid", but still interesting questions. > Interesting for me. :-) > > In MySQL there was type called INT UNSIGNED, where I used > to keep values from 1 to 4294967295 without any problems. > 1 is '0001' in binary, and > 4294967295 is ''. > > I also was able to do f.e. 'SELECT xxx WHERE (xxx & 2147483648)' > and get everything I want. > 2147483648 is '1000' in binary. > > Nevertheless, with PostgreSQL I have troubles. I didn't find > any type which will help me to do everything above mentioned. > > May be someone of you was in the same situation, or has enough > knowledge to help me with that? I think all of the postgres types are signed. An int8 will store the values (at the cost of alot of extra bits). I think if you were willing to do a little coding you'd probably be able to make a uint4 type (but I don't know what'd be involved in actually doing that) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] rollback
Hi everybody, I was playing with psql and accidently deleted a couple of records from my database. I am wondering if there is any way to restore them. I know that in Oracle you can do 'rollback work' from SQLPlus interface and it would rollback all the updates done to the database. I am pretty sure that from now on I would try to revoke permissions to delete anything from the database from users like myself. What is the best way to do this? thanks, Oleg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] URGENT: restoring a database
Hi, I think I got a problem here. I tried to restore my database from dump.bac file, which was created with pg_dumpall -o > dump.bac This is what I did: > createdb replica > psql -d replica -f dump.bac Notice that I have two different databases stored in this file. This is what I got: You are now connected to database template1. DELETE 3 psql:db_10_22_01.bac:7: ERROR: CREATE USER: permission denied psql:db_10_22_01.bac:8: ERROR: CREATE USER: permission denied psql:db_10_22_01.bac:9: ERROR: CREATE USER: permission denied psql:db_10_22_01.bac:11: ERROR: pg_aclcheck: invalid user id 503 You are now connected to database template1 as user postgres. psql:db_10_22_01.bac:18: ERROR: CREATE DATABASE: database "webspectest" already exists You are now connected to database webspectest as user postgres. CREATE DROP You are now connected as new user postgres. psql:db_10_22_01.bac:48: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'activitytype_pkey' for table 'activitytype' psql:db_10_22_01.bac:48: ERROR: Relation 'activitytype' already exists psql:db_10_22_01.bac:65: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'dcr_pkey' for table 'dcr' Obviously, no database was created. Moreover, I can not access my neither of my existing databases anymore. When I try: > psql webspectest I get an error: psql: FATAL 1: user "olebedev" does not exist At this point I am completely stuck. Please help. thanks, Oleg ---(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
