Re: [SQL] Create table in functions
Hi, there, I don't think that you can use DDL in PL/pgSQL, normally say , you can only use DML in PL/pgSQL. i.e. you can use select into,update,delete,insert ... , but you cannot use create, alter, grant ... Andreas Tille wrote: > Hello, > > I striped down my function up to a strange thing: > > web=# create function pHelpTable( ) > web-# returns int > web-# As ' > web'# Begin > web'# Create Table Temp_NumTable ( Num int ) ; > web'# > web'# return 17 ; > web'# End; ' > web-# language 'plpgsql' ; > CREATE > web=# > web=# select pHelpTable( ); > ERROR: copyObject: don't know how to copy 611 > web=# > > What does this mean? The ERROR is caused by the Create Table statement > (when I removed it from my complex function it worked well). > So why doesn't this work and what copy function fails here?? > > Kind regards > > Andreas. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Optimizing huge inserts/copy's
Hi, there, 1. use copy ... from '.'; 2. write a PL/pgSQL function and pass multiple records as an array. However, if your table have a foreign key constraint, it cannot be speed up, I have same question as you, my table invloving 9-13 million rows, I don't know how can I add a foreign key them also? Webb Sprague wrote: > Hi all, > > Does anybody have any thoughts on optimizing a huge > insert, involving something like 3 million records all > at once? Should I drop my indices before doing the > copy, and then create them after? I keep a > tab-delimited file as a buffer, copy it, then do it > again about 400 times. Each separate buffer is a few > thousand records. > > We do this at night, so it's not the end of the world > if it takes 8 hours, but I would be very grateful for > some good ideas... > > Thanks > W > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.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
Re: [SQL] Create Primary Key?
Hi, It suppose to use ALTER TABLE tablename ADD constraint xxx primary key(columnname), unforturatly, Pg7.0 still haven't implemented it yet, so except foreign key ,other constarints, you have to choose: 1. rename your old table, recreate an new one with primary key, then use insert into newtable select * from oldtable. 2. use pg_dump -t tablename -f scriptfile dbname in shell , edit that scriptfile, add primary key there, drop your table, then reload it again: psql dbname Apropos of my last question: > > Is there syntax to create a primary key after the > table has been defined and populated? I think I could > speed things up quite a bit by not having any indexes > at all when I do my mass copies. > > Thanks, and my apologies if that is a totally stupid > question. > > W > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.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
Re: [SQL] Optimizing huge inserts/copy's
Hi, there, I tried different ways, include vaccum table , ensure index works, it still is as slow as ~100rows per minute. Stephan Szabo wrote: > On Tue, 29 Aug 2000, Jie Liang wrote: > > > Hi, there, > > > > 1. use copy ... from '.'; > > 2. write a PL/pgSQL function and pass multiple records as an array. > > > > However, if your table have a foreign key constraint, it cannot be speed > > up, > > > > I have same question as you, my table invloving 9-13 million rows, I > > don't > > know how can I add a foreign key them also? > > I haven't tried it on really large tables, but does it turn out faster to > use ALTER TABLE ADD CONSTRAINT to add the foreign key constraint after the > data is loaded and the indexes are created? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Optimizing huge inserts/copy's
Hi, I knew that if no constarint, it populate very quick, my question is: when two tables have been reloaded, then I want to add a foreign key constraint to it, say: tableA has primary key column (id) tableB has a column (id) references it, so I say: ALTER TABLE tableB ADD CONSTRAINT distfk FOREIGN KEY (id) REFERENCES tableA(id) ON DELETE CASCADE ; It just seems takes forever. Thanks anyway. Webb Sprague wrote: > I am experimenting with this too. If I have any > indexes at all, the copy's get VERY SLOW as the table > gets big. Delete ALL your indexes, do your copy's, > and then create your indexes again. > > Good luck. > --- Jie Liang <[EMAIL PROTECTED]> wrote: > > Hi, there, > > > > I tried different ways, include vaccum table , > > ensure index works, it > > still is as slow as ~100rows per minute. > > > > > > Stephan Szabo wrote: > > > > > On Tue, 29 Aug 2000, Jie Liang wrote: > > > > > > > Hi, there, > > > > > > > > 1. use copy ... from '.'; > > > > 2. write a PL/pgSQL function and pass multiple > > records as an array. > > > > > > > > However, if your table have a foreign key > > constraint, it cannot be speed > > > > up, > > > > > > > > I have same question as you, my table invloving > > 9-13 million rows, I > > > > don't > > > > know how can I add a foreign key them also? > > > > > > I haven't tried it on really large tables, but > > does it turn out faster to > > > use ALTER TABLE ADD CONSTRAINT to add the foreign > > key constraint after the > > > data is loaded and the indexes are created? > > > > -- > > Jie LIANG > > > > Internet Products Inc. > > > > 10350 Science Center Drive > > Suite 100, San Diego, CA 92121 > > Office:(858)320-4873 > > > > [EMAIL PROTECTED] > > www.ipinc.com > > > > > > > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.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
Re: [SQL] Re: Order by in stored functions
Hi, My experience is when you involoved that you have to use some CONTROL LANGUAGE such as LOOP, IF ... ELSE ... or value transfer (use variables), then using function, otherwise using view, temp table... In Postgres, function is another way to store procedure. Andreas Tille wrote: > On Mon, 4 Sep 2000, Tom Lane wrote: > > > This is a bug that has already been fixed in current sources: the check > > that makes sure your select produces only one varchar column is > > mistakenly counting the hidden IdSort column that's needed to sort by. > Is there any patch against 7.0.2 sources which might help me (or the > Debian package maintainer out? > > > I don't know of any good workaround in 7.0, short of patching the > > erroneous code. Have you thought about using a view, rather than a > > function returning set? > I could try that. > > Is there any general advise for more or less beginners like me regarding > when to use views and when to use functions? > > Kind regards > > Andreas. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Query on array-elements inside object
Hi, there I believe that if you create a function with PL/pgSQL that take a text array as parameter: create function cancer(_text) returns bool as ' declare . begin use a while loop here to scan the whole array if string match 'cancer' immediately return true otherwise end the loop return false end; ' language 'plpgsql'; Umashankar Kotturu wrote: > Hi, > > I am new to Postgres as well as object database. Wondering if one of you can > tell me on how to write a sql query to select an object that has a > particular element in an array. > > Example > > If I create an object-table with 2 elements. > a) PatientID -> numeric > b) DiseaseArray -> array of text > > - This DiseaseArray has elements like "cancer", "tb" etc... > > Now how will I write a sql-query to select all patients that have "cancer" > > Any pointers will be helpful. > > regards, > Uma. > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > Share information about yourself, create your own public profile at > http://profiles.msn.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
Re: [SQL] 7.0.2: Arrays
Hi, there Your syntax is not correct, pls check the Pg documentatation, the correction as following. Larry Rosenman wrote: > Greetings, > I was trying to use arrays today, and can't seem to get it right. > > What am I doing wrong? > > ler=# create table ia_standby (hsrp_group int2, > ler(# router_interfaces[] varchar(64), ==>router_interfaces varchar(64)[], > > ler(# routers[] varchar(64)); > ERROR: parser: parse error at or near "[" > ler=# create table ia_standby (hsrp_group int2, > ler(# router_interfaces[] text, ==>router_interfaces text[], > > ler(# routers[] text); > ERROR: parser: parse error at or near "[" > ler=# > > -- > Larry Rosenman http://www.lerctr.org/~ler > Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] > US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Protection of tables by group and not by users
Hi there, I don't quite know what really want to do, however, postgres support group , so you can create a group one time, then grant or revoke this group of people from an object just need the group name. see CREATE GROUP groupname [EMAIL PROTECTED] wrote: > > Hello, > > Is it possible to protect a table of Postgresql by a group of persons instead of >giving a list > of persons ? > > Thanks for your answers > > Regards > nicolas michaud -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Cascading Deletes
Hi, there, 2 ways may resolve you problem, I believe: 1. re_create your table set _ID as primary key and ID as foreign key with option ON DELETE CASCADE if _ID is unique key. 2. create a rule bind this table, for each row deleting(or create a trigger). Craig May wrote: > Hi, > > I have a tables having this structure: > > ID (int) | _ID (int) | Name (String) > > _ID is the parent of ID. > > I'm looking for the best method to perform a cascade delete. For example, I > delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it > would continue through the chain. > > For example: > > 0 0 Base > 1 0 Sib1 > 2 0 Sib2 > 3 0 Sib3 > 4 1 Sib1_1 > 5 1 Sib1_2 > > Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 > and Sib1_2. > Can anyone help out here? > > Regards, > Craig May > > Enth Dimension > http://www.enthdimension.com.au -- 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] Odd stuff
Hi, there I hava a very odd problem, I have table named categories, it's been used for a long time, nobody change anything on it, I have a script to read it out every day, however, I got Permission deny, those script has been used for a long time. When I want to check the permission of the table by urldb=# \d categories Table "categories" Attribute |Type | Modifier -+-+-- cid | integer | not null name| varchar(32) | code| varchar(10) | description | text| super | integer | Index: categories_pkey urldb=# \z categories ERROR: aclitemout: bad ai_idtype: 53 What this message means? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Group by within table joins
Hey, If you use group then except aggreate functions(count,sum..) other items in your select list should be in your group list also. Bernie Huang wrote: > Hi, I have the following SQL: > > SELECT ltb.v_id, >count(ltb.v_id) AS num_of_times_borrowed, >vtb.equip_attr[1] AS year, >vtb.equip_attr[3] AS model, >vtb.equip_attr[4] AS type > FROM log_tb ltb, vehicle_tb vtb > WHERE ltb.v_id=vtb.equip_id > GROUP BY ltb.v_id > ORDER BY year; > > "ERROR: Attribute vtb.equip_attr must be GROUPed or used in an aggregate > function" > > but, it didn't work. I want to know how many time each vehicle has been > borrowed. Please help. Thanks. > > - Bernie -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] get table schema in psql?
Hi, there, You just need type psql> \d tablename use \? to get help Michael Teter wrote: > how can I get the schema of a table in psql? > > in oracle I would do "desc tablename". I remember sql > server and ingres having a similar facility. > > right now the only way I seem to be able to get the > information is by using pgaccess. > > I realize the definition of the table is probably > spread across the various pg_ system tables, but I'm > thinking there must be some easier way to get the > info. > > in particular, I'm after the list of columns, their > data types and lengths, null/non-null, default values, > and table indices. > > thanks, > michael > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.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
Re: [SQL] Error with DISTINCT
Hi,
try this:
testdb=#SELECT DISTINCT table_2.f1, table_1.f2, date('2000-08-22') AS
testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1;
Jerome Raupach wrote:
> testdb=#SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22' AS
> testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1;
> ERROR: Unable to identify an ordering operator '<' for type 'unknown'
> Use an explicit ordering operator or modify the query
>
> I must execute this query :
>
> testdb=#SELECT table_2.f1, table_1.f2, '2000-08-22' AS date FROM
> testdb-#table_1, table_2 WHERE table_1.f1 = table_2.f1 GROUP BY
> testdb-#table_2.f1, table_1.f2;
>
> Is it a bug ?
> Thanks in advance.
>
> Jerome.
--
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] info about tables
Hi, I want drop some tables, is there any way to get the info which can show when the tables were used last time? I mean that I want know when this table is used last time? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Division
Hi, there, I don't quit sure what you really want to solve, however, if you use EXCEPT results1 and result2 should be same type rather than tables, i.e. select a,b from tab1 except select c,d from tab2; a,c are same type b,d are same type. notes, EXCEPT to big table is not efficient, so does IN clause. Bernie Huang wrote: > Hi, > > I tried the write up the following SQL statement: > > result1 from sql1 = (1,2,3,4,5) > result2 from sql2 = (4,5) > result3 = result1/result2 (or is it result2/result1) = (1,2,3) > > What do I have to do to get the (1,2,3) result? I tried "query EXCEPT > query", but it seems like EXCEPT needs both tables in queries to be of > the same number of columns and column types. And (any, all, exist) > doesn't seem to be a right solution. > > Thanks for help. > > - Bernie -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] PLSQL
Hi, there, http://www.postgresql.org/doxlist.html I.10, III.45 Paulo Roberto Siqueira wrote: > Where can I find a tutorial on PL/SQL for postgres? Is there any > documentation focused on it on postgres' site? > > Thank you -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp
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
Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp
Hi, there, urldb=# create table foo(sec int4); CREATE urldb=# insert into foo values(54321); INSERT 382942319 1 urldb=# select interval(reltime (sec||'secs')) from foo; interval -- 15:05:21 (1 row) "Brian C. Doyle" wrote: > 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 -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] how to store a query, that results in a table
Hi, So, your question is not client side, you want store a procedure in db(server side) can accept para from client side. I have not seen Pg has a functionality to do like store procedure or package like Oracle ( maybe I don't know), so , I suggest that : 1 use embeded SQL 2. store query as a SQL in shell script( because shell script can accept parameters) in shell , you can say: echo "select * from table1 where row1=$1"|rsh pg_server /usr/local/pgsql/bin/psql -U robt db (e.g . remote shell call, local is same) 3. if you use bash shell, you can also use pgbash(http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html) Nelson wrote: thank you jie Liang for your response, but my problems are: 1. How to store a query in the database. 2. How to give a parameter from outside of database, for example: select * from table1 where row1 = my_parameter_outside. Give me an example please. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] [GENERAL] Foreign Keys Help Delete!
Hi, Timothy, Try: 1. dump out the data of routes and drop it, 2. re-build your routes table, CREATE TABLE routes ( field1 type1, fqhn stype2, CONSTRAINT if_host_exists FOREIGN KEY(fqhn) REFERENCES hosts ON UPDATE CASCADE ON DELETE CASCADE ); or use alter table add constraint . When update hosts's primary key, foreign key will be updated also, so does delete. However, I found foreign key of Pg7.0 is extremely slow for big table loading, because Pg constraint have no disable, novalidate choice, so it's not easy to use so far, I believe. Timothy Covell wrote: > DB: Postgresql 7.0.2 > OS: Solaris 2.6 > > Schema: > hosts table with fqhn column > routes table with fqhn foreign key hosts(fqhn) > > Problem: > > 1. When I try to change fqhn in hosts, it complains that > I have now violated entry in "routes" table. > > 2. When I try to update "routes" table, it updates. > > 3. Go back to "hosts" table and now try to rename/delete > old fqhn and it complains about object missing with OID=x. > > Questions: > > 1. What's the proper way to delete foreign keys? > (I can dump the DB, edit it, and restore it, but that is > not effecient!!) > > 2. How can I avoid problems such as above, besides not > using foreign keys? > > 3. Are foreign keys broken in pg7.0.2??? > > TIA > tim > [EMAIL PROTECTED] -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] select
Hi, there, only possible is null, so select blah from tableblah where field is null; Jeff MacDonald wrote: how would i select all rows where a boolean value is neither t nor f.. ? ie if someone inserted without setting the boolean tag. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Fascimile : 1 902 542 5386 IRC Nick : bignose -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Data Type precision
Hi, there, You can use function: urldb=# select round(234.356534,2); round 234.36 (1 row) Jerome Raupach wrote: > CREATE TABLE TR (f1 FLOAT4, f2 INT4, f3 INT4) ; > > UPDATE TR SET f1=f2/f3::FLOAT4 ; > > f1 -> xx,xx - but I want f1 -> xx,xx. > (6,6) (6,2) > > anybody can help me ? > Thanks. Jerome. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] copying/moving from one table to another
Hi, there, I do not quite sure is this what you want? 1. if newtable(one you want save) does not exist: select * into newtable from oldtable; or create table newtable as select * from oldtable; 2. if newtable existed, you append some record into it: insert into newtable select * from oldtable where clause; Joachim Trinkwitz wrote: Hi all, is there a handy way to copy or (preferrably) move a whole record from one table to another, equally structured table in the same DB? Background: I have some tables which hold information concerning our staff, where people quite so often come and go, because their employment contract is limited to a year or two. When deleting a person in the DB, I don't want to lose this information completely, but I want to save it in a backup table. I suppose this is a newbie question, maybe I am stirring me blind somewhere ... Greetings and thanks, jaochim -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] how to store a query, that results in a table
Hi, there,
If the client machine is a trusted machine in your company, use remote
shell 'rsh' can
call a script of SQL.
If not , you had better use embeded SQL.
I don't know how visual basic embed SQL, but I think is same way as
other language,
in postgres:
#db> create user robot with password 'wowwow';
$db> grant all on table1 to robot;
In perl , it's very similar like this:
use Pg;
my $conn;
sub connectDb {
$conn
= Pg::connectdb("dbname=db host=dbserver port=5432 user=robot password=wowwow");
if ( $conn->status
!= Pg::PGRES_CONNECTION_OK ) {
die "Cant open postgres! : " . $conn->errorMessage . "\n";
}
return;
}
#main
connectDb();
my $query="select * from
table1 where row1=parameter1";
print $query,"\n";
my $res=$conn->exec($query);
$res->cmdStatus || die $conn->errorMessage.":$!\n";
my $rows= $res->ntuples;
for (my $k = 0 ; $k <
$rows; $k++){
my $field1=$res->getvalue($k,0);
my $field2=$res->getvalue($k,1);
print $field1,"\t",field2,"\n";
}
root wrote:
hi.
I have a problem.
I require to store a query sql in postgresql-7.0 like:
example.
select * from table1 where row1(table1)=parameter1
If i execute this query directly, I don't have problem.
I want to store this query in order to execute from a client program
(visual basic 6.0), but i don't know how to?
I tried to store the query like a function (create function ...), but
it
was impossible to find a way in order to obtain a table like result.
Only we could obtain a single row by using the rtype " returns setof
varchar".
I hope a soon answer.
Thank you.
Nelson B.
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
Re: [SQL] if else query help
Hey, there, This is very interesting. I have similar problem: I want drop some junky table in my database, how can I detect a table when last time it is used. I try to say that I want to know how long this table has NOT been used at all. I don't which system table holds this statistics. Josh Berkus wrote: > Brian, Jean-Christophe, > > >Someone corrects me if I'm wrong, I come from the Oracle world... > > > > Dates (or I should say TimeStamps) are stored as floating point values > > : the integer part is the number of days since a certain date > > (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the > > portion of the day (although I don't know --yet-- how to convert > > date2-date1 to an integer, trunc does not work). > > You're doing this the hard way. One of Postgres' best features is its > rich collection of date-manipulation functions. Please see: > > ... H. The online docs appear to be down. When they're back up, > please check the sections on: Date/Time data types, and Date/Time > manipulation functions. > > -Josh Berkus > > P.S. Brian, a general tutorial on writing SQL, such as O'Reilly's > soon-to-be released SQL book, might help you a great deal. > > -- > __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 -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Standard syntax?
Hi, This is not SQL92, I believe it's an extention of Pg , Oracle uses 'decode' to implement if-then elsif then-else structure. Franz J Fortuny wrote: > I have been using (with success) this SQL statement in PostgreSQL: > > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > > The above syntax, however, does not work for Interbase (6.01). For those who > have had experience in other SQL servers, is this a "standard" or ANSI 9X > compatible syntax or should I refrain from such syntax if I want my > statements to be transportable from SQL Server to SQL Server? > > Best regards, > > Franz Fortuny -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] COUNT
Hi, there, You want how many rows in your table??? select count(*) from yourtablename; 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 -- 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] How to call a shell command in rule
Hi, I want send a e-mail when the rows of mytable reaches 100,000, how? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Problem whith Stored queries
> Hi, anybody know how to call shell command in postgres rule or trigger, urgent!! -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] How to call a shell command in rule
Hi, but perl cannot be used in trigger yet :-( Clayton Cottingham wrote: > On Wed, 25 Oct 2000 10:44:48 -0700, Jie Liang said: > > > Hi, > > > > I want send a e-mail when the rows of mytable reaches 100,000, how? > > > > > > > > one way is to make a function using perl > and use say mail::sender as the module to send the info > > use the code snippet > in perldoc Mail::Sender > > another would be to build a cron perl job that would run every so often and > check out > how many rows then send email > > > -- > > Jie LIANG > > > > Internet Products Inc. > > > > 10350 Science Center Drive > > Suite 100, San Diego, CA 92121 > > Office:(858)320-4873 > > > > [EMAIL PROTECTED] > > www.ipinc.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] need help urgent
Hi,
Is anybody have experience about core dump?
We have some scripts have been used for a long time, however, recently,
they seem to have some problems,
the message I got:
SSELECT priority from priority where source=lower('questionable')
Total insert into preunchecked: 1
Segmentation fault - core dumped
Segmentation fault - core dumped
This script has been used for a few monthes, no problem.
I checked my pgsql.log, I saw:
Nov 3 00:58:45 wipeout postgres[1638]: query: INSERT INTO
source(id,source) SELECT id,'questionable' from
preunchecked WHERE insertdate>= '2000-11
-03 00:58:45-08'
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT version()
Nov 3 00:58:46 wipeout postgres[1642]: query: begin
Nov 3 00:58:46 wipeout postgres[1642]: ProcessUtility: begin
Nov 3 00:58:46 wipeout postgres[1642]: query: set transaction isolation
level serializable
Nov 3 00:58:46 wipeout postgres[1642]: ProcessUtility: set transaction
isolation level serializable
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT oid from
pg_database where datname = 'template1'
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_type.oid,
typowner, typname, typlen, typprtlen, typinput, typoutput, typreceive,
typsend, typelem, typdeli
m, typdefault, typrelid, typbyval, usename from pg_type, pg_user where
typowner = usesysid
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_proc.oid,
proname, prolang, pronargs, prorettype, proretset, proargtypes, prosrc,
probin, usename from pg_
proc, pg_user where pg_proc.oid > '17216'::oid and proowner = usesysid
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_aggregate.oid,
aggname, aggtransfn1, aggtransfn2, aggfinalfn, aggtranstype1,
aggbasetype, aggtranstype2, a
gginitval1, agginitval2, usename from pg_aggregate, pg_user where
aggowner = usesysid
We have no script to open a new session[1642], how this happen? how I
to prevent this??
Thanks!
--
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] plperl
hi, I followed README tried to install plperl: su-2.04# cd /work/src/pgsql702/src/pl/plperl su-2.04# perl Makefile.PL Writing Makefile for plperl su-2.04# make "../../../src/Makefile.global", line 135: Need an operator "../../../src/Makefile.global", line 139: Missing dependency operator "../../../src/Makefile.global", line 143: Need an operator "../../../src/Makefile.global", line 144: Missing dependency operator "../../../src/Makefile.global", line 148: Need an operator "../../../src/Makefile.global", line 149: Need an operator "../../../src/Makefile.global", line 150: Need an operator "../../../src/Makefile.port", line 1: Need an operator "../../../src/Makefile.port", line 3: Need an operator "../../../src/Makefile.port", line 6: Need an operator "../../../src/Makefile.port", line 8: Need an operator "../../../src/Makefile.port", line 16: Need an operator "../../../src/Makefile.global", line 246: Missing dependency operator "../../../src/Makefile.global", line 247: Could not find ../../../src/Makefile.custom "../../../src/Makefile.global", line 248: Need an operator "../../../src/Makefile.global", line 253: Missing dependency operator "../../../src/Makefile.global", line 255: Need an operator "../../../src/Makefile.global", line 284: Missing dependency operator "../../../src/Makefile.global", line 286: Need an operator "../../../src/Makefile.global", line 288: Missing dependency operator "../../../src/Makefile.global", line 290: Need an operator "../../../src/Makefile.global", line 292: Missing dependency operator "../../../src/Makefile.global", line 294: Need an operator "../../../src/Makefile.global", line 296: Need an operator "../../../src/Makefile.global", line 299: Need an operator "../../../src/Makefile.global", line 301: Need an operator "../../../src/Makefile.global", line 304: Need an operator make: fatal errors encountered -- cannot continue su-2.04# what I need to do? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] how to execute a C program via trigger ?
Hi, Is any other SQL implicit cursor attribute in PL/plsql ?? when you say (in pl/plsql): select field into v_1 from atable where whatever; special variable FOUND can be used to tell return is null or not. this functions like SQL%FOUND or SQL%NOTFOUND in Oracle, however, when I do some DML(insert,delete,update), is there any other special variable can tell me howmany success. Like SQL%ROWCOUNT in Orcale?? And if there is an error such as : cannot insert since duplicate key on an unique index, is it possible to catch it?? Thanks. -- 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] plpgsql
> Hi, there, Is there any way to handle exception ( such as cannot insert duplicate key on a unique index) in plpgsql function? I don't want it abort whole transaction instead I want to do something else if it happened, but I don't want to use a select stmt first to waste the time. In Orcale, in plsql we can say, declare begin do something exception do something else end; How to this exception section in plpgsql???? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Foreign key constraint
Recovery try use: pg_dump -t tbname -f outfile dbname use vi erase f key from outfile, drop old table, reload them. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] plpgsql
OO, That's a big disadvantage, because if the table is huge, using select stmt walking even on an index will take some time and duplicate occur not often, efficiency is a big problem. Thanks anyway. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 12 Dec 2000, Kovacs Zoltan Sandor wrote: > > Hi, there, > > > > Is there any way to handle exception ( such as cannot insert duplicate key on > > a unique index) in > > plpgsql function? > > > > I don't want it abort whole transaction instead I want to do something else if > > it happened, > > but I don't want to use a select stmt first to waste the time. > Bad news: there is no such statement in PLPGSQL you like. My usual way to > do this is the same you wrote (SELECT first, if no rows FOUND, do the > INSERT). > > Zoltan >
[SQL] select ... for update
> Hi,
How can I use select ... for update to update limit to update what I
select??
somewhat like:
select url,id from mytable for update order by priority,id limit 5;
I want update the id in above return like:
update mytable set allocatedto='whatever' where id in above return set.
Could I do it in one stmt.
And what is class_name in following:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
expression [ AS name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM table [ alias ] [, ...] ]
[ WHERE condition ]
[ GROUP BY column [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
[ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF class_name [, ...] ] ]
LIMIT { count | ALL } [ { OFFSET | , } start ]
can any one give me a example??
--
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] plpgsql
Hi, How can I declare an array in plpgsql?? when I use declare url text[10]; ERROR: parse error at or near "[" if I use _text; declare is OK, however, when I assgin a value after BEGIN url[i]:=whatever; get same ERROR, Is it possible to return an array from a plpgsql function?? Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Problem with function...
Hi, there, modify the code as following. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 18 Dec 2000 [EMAIL PROTECTED] wrote: > Hi, > > I hope my question is appropriate for this list. I'm trying to create > a function that calculates the distance between a pair of > latitude/longitude points. This is what I have: > > /* latlon_distance.pgsql > * by Jamu Kakar <[EMAIL PROTECTED]>, Dec 18, 2000. > * > * Calculates the distance between 2 lat/lon pairs. Syntax: > * distance (lat1, lon1, lat2, lon2) where parameters are in decimal degrees. > */ > > CREATE FUNCTION distance (float8, float8, float8, float8) RETURNS float8 AS ' > DECLARE > radius constant float8 := 6378; > distance float8; > lat1 ALIAS FOR $1; > lon1 ALIAS FOR $2; > lat2 ALIAS FOR $3; > lon2 ALIAS FOR $4; > BEGIN > distance := radius * acos ((sin (radians (lat1)) * sin (radians (lat2))) + >(cos (radians (lat1)) * cos (radians (lat2)) * > cos (radians (lon1) - radians (lon2; > -- RETURN ''distance'';-- here means text instead of float8 which -- you defined. ===> RETURN distance; > END; > ' LANGUAGE 'plpgsql'; > > When I try a: > > select distance(49.0,-122.0,50.0,-123.0) as distance; > > I get: > > ERROR: Bad float8 input format 'distance' > > I've tried a variety of ways of specifying the values and I've hunted > through the mailing lists but haven't turned up anything useful. Any > help would be appreciated. > > Thanks, > Jamu. > > -- > Jamu Kakar (Developer)Expressus Design Studio, Inc. > [EMAIL PROTECTED] 708-1641 Lonsdale Avenue > V: (604) 988-6999 North Vancouver, BC, V7M 2J5 >
Re: [SQL] SQL query not working when GROUP BY / HAVING is used
I hope it may help: 1. if you use group clause in a select stmt, the select list must be agregate function such as sum(field),count(field), max(field)..., cannot use field. 2. for field have NULL field, should use field IS NULL, = NULL will give you wrong result! Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 19 Dec 2000 [EMAIL PROTECTED] wrote: > Hello there > > I have a question regarding a SQL statement. > > When I execute (and that's what I need) > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > AND Auftrag.A_Ztyp=Zylinder.Z_typ > AND Z_A_nr = NULL > AND Z_status = 'zcu' > GROUP BY Zylinder.Z_durch_soll > HAVING durchmesserdelta >= 0.085 > ORDER BY Zylinder_Typen.Z_durch_soll desc > > I get the following error in the pgadmin.log file. > > 19.12.00 10:53:34 Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, > Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) > AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM > Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( > Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = > Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL > AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta > >= 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc > 19.12.00 10:53:34 Executing SQL Query... > 19.12.00 10:53:34 Done - 0,01 Secs. > ** > * Error - 19.12.00 10:53:34 > ** > > Software > > Program: pgAdmin > Version: 7.0.4 > Sub or Function: frmSQL, cmdExecute_Click > > Error Details > * > Error No: -2147217887 > Error Description: Der ODBC-Treiber unterstützt die angeforderten > Eigenschaften nicht. > Error Source: Microsoft OLE DB Provider for ODBC Drivers > DLL Error Code: 0 > > Memory Details > ** > Total Physical: 132435968 > Total Swap: 434098176 > Total Virtual: 2147352576 > Available Physical: 34004992 > Available Swap: 291512320 > Available Virtual: 2079350784 > Percentage Free: 0 > > System Details > ** > Processor: 586 > OEM ID: 0 > No. Processors: 1 > Page Size: 4096 > > OS Details > ** > Platform: Windows NT > Version: 4.0 > Build: 1381 > OS Info: Service Pack 5 > > Environment Details > *** > Datasource: pgmondadori > Tracking: False > TrackVer: 0 > Connect: Provider=MSDASQL.1;Extended Properties > >="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD > > >**;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS > > " > Version: 2.6 > > > Using the same statement without the GROUP BY and HAVING it is ok ! > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND (Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > ANDAuftrag.A_Ztyp=Zylinder.Z_typ > ANDZ_A_nr =NULL > ANDZ_status = 'zcu' > ORDER BY Zylinder_Typen.Z_durch_soll desc > > Whats wrong, according to the docs, the syntax is ok and it should be > possible to use these keywords! > > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > >
Re: [SQL] SQL query not working when GROUP BY / HAVING is used
Hope it helps: 1. If you use GROUP, the select list should sum|count|max ..., no single field. 2. If you use NULL, the condition should be field IS [NOT] NULL, = NULL will give the wrong answer. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 19 Dec 2000 [EMAIL PROTECTED] wrote: > Hello there > > I have a question regarding a SQL statement. > > When I execute (and that's what I need) > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > AND Auftrag.A_Ztyp=Zylinder.Z_typ > AND Z_A_nr = NULL > AND Z_status = 'zcu' > GROUP BY Zylinder.Z_durch_soll > HAVING durchmesserdelta >= 0.085 > ORDER BY Zylinder_Typen.Z_durch_soll desc > > I get the following error in the pgadmin.log file. > > 19.12.00 10:53:34 Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, > Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) > AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM > Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( > Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = > Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL > AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta > >= 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc > 19.12.00 10:53:34 Executing SQL Query... > 19.12.00 10:53:34 Done - 0,01 Secs. > ** > * Error - 19.12.00 10:53:34 > ** > > Software > > Program: pgAdmin > Version: 7.0.4 > Sub or Function: frmSQL, cmdExecute_Click > > Error Details > * > Error No: -2147217887 > Error Description: Der ODBC-Treiber unterstützt die angeforderten > Eigenschaften nicht. > Error Source: Microsoft OLE DB Provider for ODBC Drivers > DLL Error Code: 0 > > Memory Details > ** > Total Physical: 132435968 > Total Swap: 434098176 > Total Virtual: 2147352576 > Available Physical: 34004992 > Available Swap: 291512320 > Available Virtual: 2079350784 > Percentage Free: 0 > > System Details > ** > Processor: 586 > OEM ID: 0 > No. Processors: 1 > Page Size: 4096 > > OS Details > ** > Platform: Windows NT > Version: 4.0 > Build: 1381 > OS Info: Service Pack 5 > > Environment Details > *** > Datasource: pgmondadori > Tracking: False > TrackVer: 0 > Connect: Provider=MSDASQL.1;Extended Properties > >="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD > > >**;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS > > " > Version: 2.6 > > > Using the same statement without the GROUP BY and HAVING it is ok ! > > SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, > ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, > (Zylinder.Z_durch_soll+0.12) AS effektiv > FROM Auftrag,Zylinder_Typen, Zylinder > WHERE Auftrag.A_nr = '11' > AND (Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND > Zylinder_Typen.Z_SW='0') > AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll > ANDAuftrag.A_Ztyp=Zylinder.Z_typ > ANDZ_A_nr =NULL > ANDZ_status = 'zcu' > ORDER BY Zylinder_Typen.Z_durch_soll desc > > Whats wrong, according to the docs, the syntax is ok and it should be > possible to use these keywords! > > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > >
Re: [SQL] question on SELECT
use: \dt -- all tables \dv -- all views \df -- all functions ... Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On 19 Dec 2000, Prasanth A. Kumar wrote: > Howard Hiew <[EMAIL PROTECTED]> writes: > > > Hi, > > I would like to know what is the sql statement that list all the tables > > name. > > > > For example in Oracle, > > 'SELECT TABLE_NAME from ALL_TABLES where owner="Oracle" '; > > > > What is the statement for Postgres? > > Thank you > > > > Best Regards, > > Howard > > CIM/MASTEC > > Tel:(65)8605283 > > You can do '\dt' to list all tables. There is also a system table > 'pg_tables' which you can use if you like to do a select instead. Do > SELECT tablename FROM pg_tables where tableowner='postgres'; > > -- > Prasanth Kumar > [EMAIL PROTECTED] >
Re: [SQL] substring ..
Hi,there, I am not sure what is your question mean. However, if the type of datefoo is a timestamp then try: select foo from table where date(datefoo) = '2000-12-14'; select foo from table where datefoo::date = '2000-12-14'::date; select foo from table where substr(datefoo,1,10) = '2000-12-14'; might work also. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 19 Dec 2000, Jeff MacDonald wrote: > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 > > any clues ? > > Jeff MacDonald, > > - > PostgreSQL Inc| Hub.Org Networking Services > [EMAIL PROTECTED]| [EMAIL PROTECTED] > www.pgsql.com | www.hub.org > 1-902-542-0713| 1-902-542-3657 > - > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt >
Re: [SQL] Create table doesn't work in plpgsql
Hi,there,
I don't think you can use DDL(data definition language) in PL/SQL.
create table is not DML(data munipulation language) instead
it's a DDL.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
On Tue, 19 Dec 2000, Volker Paul wrote:
> Hi,
>
> can I do some table manipulation in plpgsql?
> Look at only the "create table" line and the error message:
>
> create function plural (text) returns text as '
>begin
>create table tmp (num int4);
>return $1 || ''s'';
>end;' language 'plpgsql';
> select plural('test');
> CREATE
> ERROR: copyObject: don't know how to copy 611
>
> What does the error message mean? Where can I read more about it?
>
> Cheers, Volker
>
Re: [SQL] plpgsql ?
Hi, there, see following. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 20 Dec 2000, guard wrote: > Hi all, > > i have a question 'plpgsql' > > my code > > CREATE FUNCTION autono (text,text) RETURNS text AS ' > DECLARE > a1 ALIAS FOR $1; > a2 ALIAS FOR $1; <== $2 ?? > ret_val text; > BEGIN > select tna into ret_val from a1 where pamt_no=a2; > -- I think that table name cannot use parameter anyway in plpgsql. > RETURN ret_val; > END;' LANGUAGE 'plpgsql' > > > error run " select tna into ret_val from a1 where pamt_no=a2;" > help me ,thanks > > > > >
Re: [SQL] `~' operator and indices
Hi, there, Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 20 Dec 2000, Artur Rataj wrote: > Hello, > > I would like to ask you why do `~' gives the following results, > if there is an index on `string': > > select string from indextbk_fti_fkey where string ~ '^IE'; ===> try this: where string ~ '^IE.*'; > string > > (0 rows) > > select string from indextbk_fti_fkey where string ~ '^IECIA'; > string > > IECIA > (1 row) > > `E' here is a polish letter. I have set locale to `pl_PL' before > starting postgres. > > Best regards > > Artur Rataj > >
Re: [SQL] How to set autocommit on/off
Hi, there, I think you can use : BEGIN;-- turn off any DDL stmts -- you can rollback them by ROLLBACK; END|COMMIT; -- turn on Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 20 Dec 2000, Kevin wrote: > As titled, is autocommit of Postgresql is default to be "ON", how to turn it > off then? > > Thnaks, > Kevin > > -- > - > Kevin LAM, System Analyst > Crown Development Ltd. > A Shun Tak Group Company > > Tel: (852) 2283-2132 > Fax:(852) 2283-2727 > - > >
Re: [SQL] Create table doesn't work in plpgsql
Hi,there, I am not quite sure what you try to do. However, plpgsql allows you use any DDL, most of functions defined and sql operators. I don't see the problem. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Thu, 21 Dec 2000, Volker Paul wrote: > Hi, > > > I don't think you can use DDL(data definition language) in PL/SQL. > > create table is not DML(data munipulation language) instead > > it's a DDL. > Thanks, but that leaves me with a problem. > What I really want to do is something like > select str from person where id=1234; > where str is a string that contains an expression like > famname || ', ' || givname > i.e. the final select is > select famname || ', ' || givname from person where id=1234; > I know it's possible by building the select e.g. in bash > and calling psql with it as an argument, but do you see a possibility > that is closer to Postgres, e.g. in plpgsql? > > > Volker Paul >
[SQL] hex number
Hi, Does anybody knows that is any function can covert an inet(IP addr) type to a hex number?? Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] problem to count (distinct number)
hi,there, I don't see the problem except extra space, try: SELECT COUNT(DISTINCT data) FROM media; Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On 17 Jan 2001, Mikael Hedin wrote: > Hi, > > I have a table with a column (int4), and I want to know how many > different numbers there are. In an example book I saw the query > > SELECT COUNT (DISTINCT data ) FROM media; > > by then I get > > ERROR: parser: parse error at or near "distinct" > > Is the query in error or does PostgreSQL not support this? How do I > then get the number of distinct data from my table? > > TIA, > > Micce > > > -- > Mikael Hedin, MSc +46 (0)980 79176 > Swedish Institute of Space Physics +46 (0)8 344979 (home) > S-Box 812, 981 28 KIRUNA, Sweden+46 (0)70 5891533 (mobile) >
Re: [SQL] How to change the ownership of the table?
Hi, there, I believe that you can use iat least: pg_dump -t table -f out dbname then vi out change the owner (first line -- connnect ...) then drop the old table and reload new table by psql dbname < out If your table have no index or any constraint, you can use SELECT * into newtable then if you have the previlage. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 24 Jan 2001, Ramesh H R wrote: > Hai > > Please, any one guide me how to change the ownership of the table? > > Regards, > > -- > Ramesh HR > Trainee Engineer > EASi Technologies > 213, 3rd Main, 4th Cross > Chamrajpet, Bangalore - 560 018 > India > Ph.: 660 1086 / 660 2365 / 667 2984 Extn.: 155 > Facsimile: 667 5274 > www.easi.soft.net > > >
Re: [SQL] "'" in SQL INSERT statement
Hi,
Using a backslash to escape it.
insert into table(field) values('what\'s that');
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
On Thu, 25 Jan 2001, Markus Wagner wrote:
> Hi,
>
> I have some data that I wish to transfer into a database using perl/DBI.
> 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?
>
> Thanks,
>
> Markus
>
Re: [SQL] interval query.
Hi, where id= or id between 3 and 12; or where id in (3,4,5,6,7,8,9,10,11,12,); Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 31 Jan 2001, Antti Linno wrote: > Good morning. > > Is there some way to make interval query? > > Towns table(estonia towns, heh :P) > > id | name > > 1 Elva > 2 Tartu > Tallinn > 3 Tallinn/Haabersti > 4 Tallinn/Mustamae > ... > etc. > > What I need is when the town's id= I want to make query > where id= OR id=[3..12] for example. I could generate bunch of OR's > like id=3 OR id=4 ... but is there some more elegant solution? > > > Greetings, > Antti > >
Re: [SQL] Array as parameter in plpgSQL functions
e.g.
create function foo(_int4) returns int2 as'
declare
a _int4 alias for $1;
i int:=1;
begin
while a[i] loop
i:=i+1;
end loop;
return i-1;
end;
' language 'plpgsql';
you can call it by:
select foo('{1232131,12312321,3424234}');
you should get 3.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
On Wed, 31 Jan 2001, Sveinung Haslestad wrote:
> I need to pass an array to a function ( int4, variable number of elements) .
> How do i declare the parameter, and how can i tell the numer of recieved elements?
>
> Thanks
> /Sveinung
>
>
>
>
Re: [SQL] CREATE TABLE AS and ORDER BY
Hey, Try: select e.studentid, u.hoursxfer into freshhr21 from enrollmentstatus e, undergradclass u where e.studentid = u.studentid and e.classtd = '1' order by u.hoursxfer Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 5 Feb 2001, Joy Chuang wrote: > Hi, > > I tried to use CREATE TABLE AS and ORDER BY. The query is as followed: > > create table freshhr21 as > select e.studentid, u.hoursxfer > from enrollmentstatus e, undergradclass u > where e.studentid = u.studentid and e.classtd = '1' > order by u.hoursxfer > > But, it returns error message "ERROR: parser: parse error at or near > "order"". Does "create table as" support "order by" inside of it? > > I am using PostgreSQL 6.5.3. > > Thank you. > > Joy Chuang >
Re: [SQL] Search
Hi, You seem want to match string insensitively, I guess. Try: ~* 'test' -- match Test|tEst|tESt ... ~* '.*test.*' -- match whateverTesTwhatever Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 5 Feb 2001, Sebastian --[ www.flashhilfe.de ]-- wrote: > Hi > > I hope someone can help me > > My problem: > > I have make a search machine whit: > > LIKE '%$suchbegriffe[$i]%' > > but when I search Test - the search machine shows only entries > whit Test. But not test or tESt. > > (sorry for my bad english) > > Regards, Sebastian > >
Re: [SQL] Use of RETURN in pl/pgsql function
You may try like: if block end if; return somefakething; no matter this return can be reached or not. then compile will be no problem. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 7 Feb 2001, Jeff Eckermann wrote: > My script is below. > > I thought (based on recent posts) that this use of RETURN is allowed, but > when trying an insert to report_table, I get the following error: > > ERROR: control reaches end of trigger procedure without RETURN > > I have solved several problems in getting to this point, but have now run > out of ideas. I would appreciate any pointers. > > jeffe@kiyoko=> uname -a > FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 > 10:44:07 CDT 2000 > jeffe@kiyoko=> psql -V > psql (PostgreSQL) 7.0.0 > > Script: > > drop function mrr(); > > create function mrr() returns opaque as ' > > begin > > if NEW.billing_frequency = ''Monthly'' -- That's doubled single > quotes (and below as well) > > then > > return NEW; > > else > > if NEW.billing_frequency = ''Yearly'' > > then > > NEW.rate := NEW.rate/12; > > NEW.rate_override := NEW.rate_override/12; > > return NEW; > > else > > if NEW.billing_frequency = ''Semi-Annual'' > > then > > NEW.rate := NEW.rate/6; > > NEW.rate_override := NEW.rate_override/6; > > return NEW; > > else > > if NEW.billing_frequency = ''Quarterly'' > > then > > NEW.rate := NEW.rate/3; > > NEW.rate_override := NEW.rate_override/3; > > return NEW; > > end if; > > end if; > > end if; > > end if; > > end; > > 'language 'plpgsql'; > > drop trigger mrr_set_trigger on report_table; > > create trigger mrr_set_trigger > > before insert on report_table > > for each row execute procedure mrr(); >
Re: [SQL] pgsql and cursor
I just know you can use implict cursor inside the plpgsql e.g declare rec record; begin FOR rec IN select_clause LOOP statements END LOOP; end; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: > Hi all, I am not sure If it is allowed to use cursor inside pgsql functions. > If it is possible, please someone could send exact synatx how it is used. If > it is not allowed is there a way arround it? I need to do some calculations > and then return this value as text. > Thanks in advance for all your help. > Najm >
Re: [SQL] What's wrong with this function
I just know you can use implict cursor inside the plpgsql e.g declare rec record; begin FOR rec IN select_clause LOOP statements END LOOP; end; Jie LIANG St Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: > Hi all, > Here is a plpgsql function: > flipr'#create function test_cur() returns text as' > flipr'# declare > flipr'# mycur cursor for select title from songs where song_id=10; > flipr'# usrrecord; > flipr'# resultstext; > flipr'# begin > flipr'# open mycur; > flipr'# fetch next from mycur into usr; > flipr'# close mycur; > flipr'# results:= usr.title; > flipr'# > flipr'# end; > flipr'# ' language 'plpgsql'; > CREATE > flipr=# select test_cur() as Title; > NOTICE: plpgsql: ERROR during compile of test_cur near line 2 > ERROR: parse error at or near "cursor" > > What I am doing wrong? > Thanks in advance for your help. > Regards, Najm > >
Re: [SQL] What's wrong with this function
Try:
create function foo(text) returns int4 as '
delcare
tcount int4:=0;
begin
for rec IN select title, dcount from songs where artist=$1 LOOP
tcount:= tcount+rec.dcount;
END LOOP;
return tcount;
end;
' language 'plpgsql';
call it by:
db> select foo('Najm Hashmi');
it will return how many songs of 'Najm Hashmi' in your database.
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Sat, 10 Feb 2001, Najm Hashmi wrote:
> Jie Liang wrote:
>
> > I just know you can use implict cursor inside the plpgsql
> > e.g
> > declare
>
> result text;
> tcount int4;
>
> >
> > rec record;
> > begin
> > FOR rec IN select_clause LOOP
> > statements
> > END LOOP;
> > end;
> >
>
> Thank you Jie for your help. I am bit confused about how it works. I want for
> each row , obtained by select statment, get certain values and then do some
> calculations and out put that resulst eg
> for rec IN select title, dcount from songs where artist='xyz'
> tcount:= tcount+rec.dcount;
> END LOOP;
> return tcount;
> would this work ?
> Thanks again for your help.
> Regards, Najm
>
>
Re: [SQL] String Concatnation
Hi, You can use every sql function and operator in plpgsql, so v||''|''||v2 is OK. however, you cannot do: declare v,v2 text; you should do: v text; v2 text; also you initialize like: v text:=''''; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: > Hi, > How can I concatnate two varialbles, seperated by a |, that are type text > together? > v, v1 text; > some work > then > res:= v ||''|''|| v1; > this syntex does not work in plpgsql?? > Any ideas how to do it ??? > Thanks. > Najm >
Re: [SQL] combining
Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 12 Feb 2001, Frank Morton wrote: > I'll really appreciate help on this if anyone will do so. I'm > used to single-table stuff, but not sure the best way to > do things with multiple tables, but here goes: > > Given two tables with a common "id" field, first table columns: > > id > lastname > > Second table columns: > > id > type > content > > These two queries get me what I want from each table: > > select unique id from table1 where lastname='morton'; > select unique id from table2 where ((type = "pie") and (content = 'apple)); it depends what you want: 1. select id from table1 where lastname='morton' and id=table2.id and table2.type = 'pie' and table2.content ='apple'; will return you the id in the intersection of 2 sets. 2. select id from table1 where lastname='morton' union select id from table2 where ((type = 'pie') and (content = 'apple')); will return you a set1+set2; > > What is the best way to combine these into one query? > > Thanks. > > Frank > >
Re: [SQL] constraint/restrict
add an foriegn key on address(country_id), let country(id) be a primary key. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 14 Feb 2001, Olaf Marc Zanger wrote: > hi there, > > with two tables i want to make some constraint-restrictions > > create table address ( id serial, country_id int4, ); > and > create table country (id serial, ...); > > to make sure that now country-row is deleted if there is still a country_id > in address table. > > e.g. > > address: 1, 2, ... > country: 2, ... > > now country wouldn't be allowed to be deleted. > > how to do that? > > thanks fo help > > olaf > -- > soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger > Lorrainestrasse 23, 3013 Bern / Switzerland > fon:+41-31-332 9782, mob:+41-76-572 9782 > mailto:[EMAIL PROTECTED], http://www.soli-con.com >
Re: [SQL] createuser problem
run this as user 'postgres' instead of 'fion' Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 13 Feb 2001, fion yong wrote: > It gives the following error when i tried to a new user > > createuser demouser1 > Connection to database 'template1' failed. > FATAL 1: SetUserId: user 'fion' is not in 'pg_shadow' > > how should i solve this problem? > >
Re: [SQL] How to create a type ?
hope it helps. e.g. create function foo() returns setof varchar as ' select name from categories ' language 'sql'; db=# select foo() as name; name - recreation business web education questionable sex social society weapons/bombs mature humor pornography tasteless computer hacking nudity drugs lingerie/bikini profanity ... Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 13 Feb 2001, Ines Klimann wrote: > Hello, > > I am trying to understand how works CREATE TYPE, but it seems to > be too difficult for me... > > Can someone help me ? > > I have tried this : > -- > create function personne_in(text) > returns personne > as 'select $1;' > language 'sql'; > > create function personne_out(text) > returns text > as 'select $1;' > language 'sql'; > > create type personne ( > internallength = variable, > input = personne_in, > output = personne_out > ); > - > but it is full of mistakes. > > Even a simple example in language 'sql' will help me. > > Thanks, > Ines. >
Re: [SQL] Datetime Query
Try:
SELECT request_no FROM request where status_code ='C' and
(completed_date::date
between '01/01/2000'::date and '01/01/2001'::date)
actually date('01/01/2000') does same thing as '01/01/2000'::date
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Thu, 15 Feb 2001, Mark Byerley wrote:
> I need to create a query which will select a request_no between Data1 and
> Date2 so...
>
> SELECT request_no FROM request where status_code ='C' and (completed_date
> between 01/01/2000 and 01/01/2001);
>
> The problem I have run into is that the completed_date field is a datetime
> format (not by my own design) and I am having some problems extracting just
> the request_no's between those dates.
> I have tried a few extract month,day,year clauses with no success. If
> anyone has an idea I would appreciate it!
> Thanks in advance.
> Mark
>
Re: [SQL] Help Retrieving Latest Record
Subquery will do: select * from basket where Date in (select max(Date) from basket); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 16 Feb 2001, Steve Meynell wrote: > Hi, I am not sure if this went through the first time so here it is > again. > > > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number |Fruit | Date > 15Apples July 20, 1999 > 20OrangesJune 7, 2000 > 13 PearsJan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20OrangesJune 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > >
Re: [SQL] PL/PgSQL FOR syntax
Yes, e.g. declare r record; begin for r in select statement loop r.fieldname can fetch the result from the select statment row by row Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 16 Feb 2001, Roberto Mello wrote: > Hi, > > What's the correct syntax to access rows in a FOR loop? I'm writing > a PL/PgSQL doc and seem to be making a mistake somewhere. > I am referring to this: > > [<>] > FOR record | row IN select_clause LOOP > statements > END LOOP; > > How do I access the rows within the for loop? row.field? > > Thanks, > > -Roberto > -- > Computer Science Utah State University > Space Dynamics Laboratory Web Developer > USU Free Software & GNU/Linux Clubhttp://fslc.usu.edu > My home page - http://www.brasileiro.net/roberto >
Re: [SQL] pl/Perl
FYI, My choice: if involving a lot of regular expressions, pl/Perl is better; if involving a lot of SQLs or other functions(or store procedures), then pl/pgsql is better. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Jeff MacDonald wrote: > > > 1: can you call other stored procedures from within pl/Perl > > No. > > darn. > > > > > > 2: from within a pl/Perl script , can i do a select etc.. > > >i'm assuming no, because you cannot use DBI.. but just wondering > > >if there is a way.. > > Not currently. > > darn. > > > > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. > > >when i tried to install pl/perl i get this.. > > > > > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > > > perl Makefile.pl > > > make > > Try using gmake instead of make (cd /usr/ports/devel/gmake, make) > > thanks ! works great. > > > Jeff MacDonald, > > - > PostgreSQL Inc| Hub.Org Networking Services > [EMAIL PROTECTED]| [EMAIL PROTECTED] > www.pgsql.com | www.hub.org > 1-902-542-0713| 1-902-542-3657 > - > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt >
Re: [SQL] Can a function return a record set?
in plpgsql you've to use select field into a_variable from table where ...(single value return) or for record|row in select fields from table loop ... end loop; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 17 Feb 2001, John Taves wrote: > Am I correct in concluding that I can't return a record set from a function? > > For example, in MS SQL I would do: > > create procedure foo as > select * from yada > > I expected to be able to do the following in postgresql. > > create function foo (integer) returns (integer) as ' > begin > select * from yada; > end; ' > language 'plpgsql'; > > I am concluding that I can't. > > jt > > >
Re: [SQL] Trouble with CREATE FUNCTION
1.The following command tells the database where to find the shared object for the PL/pgSQL language's call handler function. CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; 2.The command CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sun, 18 Feb 2001, S Bey wrote: > Dear All, > > I have tried creating a function as follows: > > CREATE FUNCTION chkTelephone () RETURNS OPAQUE AS ' >BEGIN > IF EXISTS (SELECT * > FROM tenant t > WHERE t.areacode = NEW.areacode AND >t.telephone = NEW.telphone) THEN > RAISE EXCEPTION ''THAT TELEPHONE NUMBER ALREADY EXISTS!''; > END IF; > RETURN NEW; >END; > ' LANGUAGE 'plpgsql'; > > However I get the following error: > > ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. > Recognized languages are sql, C, internal and the created procedural > languages. > > In the documentation it states that 'PL/pgSQL is a loadable procedural > language for the Postgres database system.'. Where can I load it from and > are there any other problems that may arise? > > Cheers, Steve. >
Re: [SQL] How can i escape a '+' or a '+' in a regexp ?
select field from table where field like '%\\%%' or field like '%*%'; select field from table where field ~ '.*\\*.*' or ~ '.*%.*'; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Feb 2001, Gabriel Fernandez wrote: > Hi fellows, > > I'm trying to the following query: > > select * from areas where titulo ~ '+' or titulo ~ '*' > > and the answer is: > > ERROR: regcomp failed with error repetition-operator operand invalid > > I have tried to escape the '+' and the '*' with a backslash, as > follows: > > select * from areas where titulo ~ '\+' or titulo ~ '\*' > but the answer is the same. > > If I use the LIKE operator, then I have the problem with '%' and '_' > :-) > > As long as the values in the field can contain either '+' or '*' or '%' > or '_' I need to escape these characters. How can i do it ? > > I'm using PostgreSQL 6.5.3 on Linux Red Hat 6.2. > > Thanks, > > Gabi :-) > > > >
Re: [SQL] Need your help
e.g. Try: CREATE TABLE emp ( id int4 primary key, empname text, salary int4, last_date datetime, last_user name); CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS BEGIN update emp set last_date=''now''::timestamp where id=NEW.id; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 19 Feb 2001, Jyotsna Kypa wrote: > Hi, > I need your help on something. I have to write a > trigger (in sybase) that does this: Everytime a record > gets updated it should update a column in that record > with the current date/time. I am able to do it for the > whole table, but how do I make sure the update happens > only for that record which is being updated? Please > respond. > Thanks a bunch, > Jyotsna. > > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - only $35 > a year! http://personal.mail.yahoo.com/ >
Re: [SQL] Controlling Reuslts with Limit
My understanding: because you return a subset instead of a single value, so between 2 select ... limit ... queries. if you delete a record(say song_id=947) then insert it again. then results are different. So for a multiple users db, you should use oder by when you use limit. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 24 Feb 2001, Najm Hashmi wrote: > Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " > > When I run a query several time I get the same results as given > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > - > 945 > 946 > 947 > 948 > 949 > I just want to know what exatly --"LIMIT without ORDER BY returns random rows > from the query" --means > Regards >
[SQL] Urgent help
Tom, I think one of system file has been crupted. I tried to drop some user then db is hangged there forever, so kill that session, when I relogin and type urldb=# \z categories NOTICE: get_groname: group 2 not found The connection to the server was lost. Attempting reset: Failed. !# and new user added cannot retrive data from any table. what I can do?? Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Tom Lane wrote: > Jie Liang <[EMAIL PROTECTED]> writes: > > My choice: > > if involving a lot of regular expressions, pl/Perl is better; > > if involving a lot of SQLs or other functions(or store procedures), > > then pl/pgsql is better. > > Also consider pltcl, which has pretty nearly perl-equivalent regexp > support, and can do queries too. Besides which it's easier to build/ > install than plperl. > > It's a shame that plperl doesn't yet have support for making queries. > It hasn't really progressed much past the proof-of-concept stage IMHO, > but no one is working on it :-( > > regards, tom lane >
[SQL] sysfile cruptted?
Tom,
I even cannot drop a user also,
urldb=# drop user sarah;
ERROR: DROP USER: group "manager" does not exist
urldb=#
urldb=# select * from pg_group;
groname | grosysid | grolist
-+--+
manager |5 | {1041,45,21}
engineering |3 | {1041,1042,21,23,42,22}
generic |4 | {1009,1044,36,1045,1043}
analysts|2 | {1048,1015,1004,1012,1014,45,1047}
urldb=# select * from pg_user where usename='sarah';
usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
passwd | valuntil
-+--+-+--+--+---+--+--
sarah | 1047 | f | f| f| f |
|
but user sarah is not a memeber of manager at all!!
What I can do??
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Wed, 21 Feb 2001, Tom Lane wrote:
> Jie Liang <[EMAIL PROTECTED]> writes:
> > My choice:
> > if involving a lot of regular expressions, pl/Perl is better;
> > if involving a lot of SQLs or other functions(or store procedures),
> > then pl/pgsql is better.
>
> Also consider pltcl, which has pretty nearly perl-equivalent regexp
> support, and can do queries too. Besides which it's easier to build/
> install than plperl.
>
> It's a shame that plperl doesn't yet have support for making queries.
> It hasn't really progressed much past the proof-of-concept stage IMHO,
> but no one is working on it :-(
>
> regards, tom lane
>
Re: [SQL] Urgent help
another weired thing is, when I: select * from pg_group; it takes about 2 minites to return, it took just a tick before, it seems that some sys tables has been locked for a timeout. Thanks. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com
Re: [SQL] underscore problem
if your table is table name is case sensitive, you should double quota it, since postgres take lower case as default. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 27 Feb 2001, postgresql wrote: > Is there anywhere in the docs that says we should not use an > underscore in a table name? > > table FC_Client_Info exists with a record where jobno 1234 has > info: > > select acode from FC_Client_Info where jobno = '1234'; > fails with a warning that fc_client_info does not exist > notice the lower case. If I quote the table > > select acode from "FC_Client_Info" where jobno = '1234'; > the select works fine > > What's the problem here? This should be easy. > > Ted > >
Re: [SQL] Passing table names to PL/PGSQL for SELECT/UPDATE/INSERT
Unfortunately, PL/PGSQL cannot pass table name. I have same problem, only thing I can do is pass an integer, then use IF .. THEN .. ELSE .. END IF; Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 3 Mar 2001, Gerald Gutierrez wrote: > > I've written my Dijkstra's algorithm in PL/PGSQL. It didn't turn out to be > a big deal at all actually, programming-wise. I understand execution speed > will be poor but it shouldn't be any slower than having something else, > like PHP or Java, execute logic and query the database. > > I'd like to generalize my function. As per Richard Huxton's suggestion to > create tables named after session ID (thanks Richard)s, I'd like to pass in > some table names so that the algorithm can read from and write into tables > that I specify as parameters to the function. Sometihng like: > > select dijkstra(inputtablename, outputtablename); > > I've tried typing the parameters as TEXT, and then just inserting $1 in the > select (e.g. SELECT * from $1 ...) This doesn't work, nor did a number of > other ways I tried. > > How can this be done? > > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
Unfortunately, in plsql you only can do DML(select/update/insert) instead of DDL(create/grant..). i.e. you cannot create a table in plsql. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 3 Mar 2001, Gerald Gutierrez wrote: > > I don't seem to be able to create tables (persistent or temporary) from > within a PL/PGSQL function. With the following script, I can create the > function fine: > > CREATE FUNCTION tst() > RETURNS INTEGER > AS ' > BEGIN > CREATE TABLE ttt(a int); > RETURN 0; > END; > ' > LANGUAGE 'plpgsql'; > > but when I execute it , I get the following error. The error happens > regardless of whether I use CREATE TABLE or CREATE TEMPORARY TABLE: > > t1=> \i tst.sql > DROP > CREATE > t1=> select tst(); > ERROR: copyObject: don't know how to copy 611 > > Is it somehow wrong to create tables from within a PL/PGSQL function? > > > Incidently, is it possible to generate a guaranteed unique table name while > creating tables, much like some UNICES' ability to generate guaranteed > unique file names for tempoary files? > > Thanks. > > > ---(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 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 do I use text script containing SQL?
if you are a user with privalege can create table psql -e dbname I want to build my tables by placing all the sql > statements in a file. What is the correct way to use > this file with psql? > > Example: My text file has this in it: > > CREATE TABLE table1 ( >table1_id serial, >field1 char(5), >PRIMARY KEY (table1_id) > ); > > I want to be able to use the file to create my table. > I've tried psql -d databasename -e < filename.txt > but that doesn't work. > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(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] Comparing dates
I think if you cast it then works. e.g. '02-03-2001'::date '02-03-2001'::timestamp Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 6 Mar 2001, Markus Fischer wrote: > Hello, > > I've a SELECT statement on many joined Tabled and one of them has > a date column called 'date_date'. When I fetch a date e.g. > '02-03-2001', I get, say, 60 results back. When I now perform the > same query with another date, lets take '03-03-2001', I get back > about 70 results. > > When I now modify my query to get both results in one I write > > SELECT > > FROM > .. > AND > date_date >= '2001-03-02' > AND > date_date <= '2001-03-03' > AND > > > I think I should get back the rows for both days, 60 + 70 makes > 130 to me. But what I get back is even smaller then 60. I > allready tried TO_DATE conversion, an OR construct but always > the same result. > > Is there something special to know when comparing/working with > date-datetypes ? > > > kind regards, > Markus > > -- > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > EMail: [EMAIL PROTECTED] > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Date question
you can say: (now() + '1year'::timespan)::date Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 6 Mar 2001, Boulat Khakimov wrote: > Hi, > > Im a little bit stuck here. > > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. > > > Regards, > Boulat Khakimov > > > -- > Nothing Like the Sun > > ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] how to get info of function
Hi, What system table hold info of user defined function?? Acturally , I want to know how to retrive the defination of a function from its name. Thankes. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] how to reload a function
I have 2 plpgsql defined functions, say: create function A() returns ... as' . 'language 'plpgsql'; create function B() returns ... as ' declare begin select A(..) into myvar from end; 'language 'plpgsql'; If I modify function A (drop && re_create), then I have to re_create function B though no change to function B. Is there any way (sql stmt) let me re_load function B's defination without using drop and create?? Thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/PgSQL and NULL
I think that is a bug in plpgsql, when passing a NULL into a plpgsql defined function, it treats other arguments as NULL also, you can use raise notice in your function to watch this buggy thing(see following). Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sun, 11 Mar 2001, Andrew Perrin wrote: > Greetings- > > I'm trying to write what should be a simple function that returns the > minimim of two integers. The complication is that when one of the two > integers is NULL, it should return the other; and when both are NULL, it > should return NULL. Here's what I've written: > > CREATE FUNCTION min(int4, int4) > RETURNS int4 > AS 'BEGIN raise notice ''arg1 is % arg2 is %'',$1,$2; -- debugging > IF $1 ISNULL > THEN > RETURN $2; > ELSE >IF $2 ISNULL >THEN > RETURN $1; >ELSE > IF $1 > $2 > THEN > RETURN $2; > ELSE > RETURN $1; > END IF; >END IF; > END IF; > END;' > LANGUAGE 'plpgsql'; > > and here's what I get: > > fgdata=# select min(10, NULL); > min > - > > (1 row) > > so it looks like, for whatever reason, it's returning NULL when it should > be returning 10. Can anyone offer advice? > > Thanks. > > -- > Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology > Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin > [EMAIL PROTECTED] - [EMAIL PROTECTED] > > > ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] pl/Perl
Tom, 1.Where or how I can get pltcl.so? I have not find this file anywhere in my source except a pltcl.c. 2.Dose installation same as plpgsql? i.e. CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' HANDLER pltcl_call_handler LANCOMPILER 'PL/pgtcl'; 3.Where I can find more doc about pltcl? Thanks for your time. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Tom Lane wrote: > Jie Liang <[EMAIL PROTECTED]> writes: > > My choice: > > if involving a lot of regular expressions, pl/Perl is better; > > if involving a lot of SQLs or other functions(or store procedures), > > then pl/pgsql is better. > > Also consider pltcl, which has pretty nearly perl-equivalent regexp > support, and can do queries too. Besides which it's easier to build/ > install than plperl. > > It's a shame that plperl doesn't yet have support for making queries. > It hasn't really progressed much past the proof-of-concept stage IMHO, > but no one is working on it :-( > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] drop table in PL/pgSQL
You cannot CREATE|DROP ALTER table in PL/pgSQL, in general, plsql can only take DML(i.e. SELECT| INSERT|UPDATE..) Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, datactrl wrote: > When I use "drop Table ..." in PL/pgSQL, it always causes an error as > "ERROR copyObject: don't know how to copy 614" > > JACK > > > ---(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 > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SOME PL/PGSQL PROBLEMS
You blame something should not be blamed. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, datactrl wrote: > I found there are some problems with PL/pgSQL. > 1) 'Drop Table' doesn't work at all In general, PLSQL can only take DML instead of DDL. > 2) '--' comment causes parsing error Not true. > 3) Execute doesn't work at all. It always causes parsing error! I havn't tested, I cannot say. > > Some questions > 1) Can I use variable in FROM clause for a table name? No, table name cannot be a variable except in execute statement. > 2) Can I use variable in WHERE such as xxx = variable? Same as 1). > > JACK > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] drop table in PL/pgSQL
Hmm, I didn't know that, this general idea from Orcale plsql, So, I assume that you can SELECT somefield into a_new_table FROM a_old_table in pg 7.1??? Thank you. No DDL can be roll back. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, Roberto Mello wrote: > On Fri, Mar 23, 2001 at 09:52:56AM -0800, Jie Liang wrote: > > > > You cannot CREATE|DROP ALTER table in PL/pgSQL, > > in general, plsql can only take DML(i.e. SELECT| > > INSERT|UPDATE..) > > You can't? I just did (on PG 7.1). > AFAIK, you _can_ CREATE/DROP, but you can't roll back. > > -Roberto > -- > +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ > Roberto Mello - Computer Science, USU - http://www.brasileiro.net > http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer > "Carrier detected." Go to the dentist... > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] About Raise Exception
At least raise exception will abort the transaction but raise notice does not. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 26 Mar 2001, datactrl wrote: > According to user guide, both Raise Notice & Raise Exception will write > message to database log. Which system table is the database log about? > > By the way what is the difference between Raise Notice & Raise Exception? > > > Jack > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] about raise exception
Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 26 Mar 2001, Jack wrote: > According to user guide, both Raise Notice & Raise Exception will write > message to database log. Which system table is the database log about? I believe it means write a message to your log file such as /.../pgsql.log > > By the way what is the difference between Raise Notice & Raise Exception? > raise notice does not terminate program but raise exception does. > > Jack > > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] using for rec inside a function: behavior very slow
I have a function: CREATE FUNCTION hasdup(text) RETURNS int4 AS ' declare v_id int4; rat1 text; rat2 text; v_urltext; rec record; begin select id into v_id from urlinfo where url = $1; if NOT FOUND then return -1; end if; select codestr(v_id) into rat1; v_url:= $1||''%''; for rec in select id,url from urlinfo where url like v_url order by url loop raise notice ''%'',rec.url; select codestr(rec.id) into rat2; if rec.id <> v_id and rat1 = rat2 then update urlinfo set list = 1 where id = rec.id; return rec.id; end if; end loop; return 0; end; ' LANGUAGE 'plpgsql'; 'where url like clause' is very slow in inside the function, but when I directly use this statement in SQL, it is very quick, is any quick way to return match: where field like 'something%' inside the plsql function?? Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] select statement inside a function: behavior bad
I tested select statement inside sql and plpgsql function, very slow CREATE FUNCTION geturllike(text) RETURNS SETOF text AS ' SELECT url as url FROM urlinfo WHERE url LIKE $1; 'LANGUAGE 'sql'; CREATE FUNCTION hasdup(text) RETURNS int4 AS ' declare v_id int4; rat1 text; rat2 text; v_urltext; rec record; begin v_url:= $1||''%''; for rec in select id,url from urlinfo where url like v_url order by url loop raise notice ''%'',rec.url; end loop; return 0; end; ' LANGUAGE 'plpgsql'; Why so slow Is it a bug?? Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Does pg_dump stable on v7.0
When I dump out my whole db with pg_dump -x dbname > dbname.out
then when I reload them, one of creation always failed
msg is relation 'urlinfo' is not exist,
so I dump out scheme first -- pg_dump -x -a dbname > dbname.out.s
reload them , samething happend, so I cut & paste the definition of
this table, it's OK. then load data is OK.
but how come??
foollowing is this table:
CREATE TABLE "urlinfo" (
"url" text NOT NULL,
"id" int4 NOT NULL,
"ratedby" character varying(32),
"ratedon" timestamp DEFAULT "timestamp"('now'::text),
"comments" text,
"list" int2,
"pidwsr" int4,
CONSTRAINT "host_ck" CHECK ((urlpart('host'::text,
(url)::"varchar") <> '*.com'::text) AND (urlpart('host'::text,
(url)::"varchar") <> '*.net'::text)) AND (urlpart('host'::text,
(url)::"varchar") <> '*.gov'::text)) AND (urlpart('host'::text,
(url)::"varchar") <> '*.*'::text)) AND (urlpart('host'::text,
(url)::"varchar") <> '*'::text))),
PRIMARY KEY ("id")
);
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] select ... for update in plpgsql
Hi, I have a question about 'select ... for update'; according to the docs, clause 'for update' will lock selected rows, I believe it should be put into a begin; select ... for update; update ...; end; block. however, if I use it in a plpgsql function, do I need another pair of begin...end? or say begin...end in plpgsql can lock chosen rows until updated ALL ROWS? if not, do you have any suggestion? Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] plpgsql
I 've a question about begin...end in plpgsql does sql stmts in begin end; will go one transaction? i.e. begin...end have same meaning as sql stmts BEGIN...COMMIT?? if failed, transaction abort? if select..for update is used then another update stmt will wait on the same rows?? if begin...end in plpgsql connot have same functionality as sql, how can I ensure my sql stmts go one transaction?? thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] plpgsql
Roberto, Thanks for your help, I read the docs. I am still not entire sure, my problem is in my plpgsql function I used for rec in select ... for update loop update stmt end loop; I don't want above chosen rows be selected(i.e. I want them to be lock exclusively) by another user until transaction done. do I need an explicit LOCK stmt? can it be used in the plpgsql function?? Thanks again. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 20 Apr 2001, Roberto Mello wrote: > On Fri, Apr 20, 2001 at 04:58:02PM -0700, Jie Liang wrote: > > > > I 've a question about begin...end in plpgsql > > does > > sql stmts in > > begin > > > > end; > > will go one transaction? > > Read the documentation (programmer's guide). It's all there. It's to > answer your questions that we take the time to write docs in the first > place :) > Short answer: everything in your function is executed in one > transaction. BEGIN and END in PL/pgSQL are NOT the same as in the > transaction semantics. > > -Roberto > -- > +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ > Roberto Mello - Computer Science, USU - http://www.brasileiro.net >http://www.sdl.usu.edu - Space Dynamics Lab, Developer > If at first you don't succeed, destroy all evidence that you tried. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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] random rows
How I can return random N rows from my select stmt? like: e.g. what my selectee is a set of 1000 rows, I want randomly pickup 100 of 1000. thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
