Re: [SQL] shared_buffers and shmall,shmmax

Tom, Sorry, it's 4kb, that explain it. Thank you, Regards, Jie Liang -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, January 25, 2007 12:15 PM To: Jie Liang Cc: pgsql-sql@postgresql.org Subject: Re: shared_buffers and shmall,shmmax > shmall is 65

[SQL] shared_buffers and shmall,shmmax

s is much lower then shmmax(536870912 bytes), and max_connection is set to 125 still failed to start postmaster? Thanks, Best regards, Jie Liang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] [JDBC] Prepare Statement

Does plperl catch the plan also? Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 2:47 PM To: Jie Liang Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] Prepare Statement On Fri, 18 Jun 2004, Jie Liang wrote

Re: [SQL] [JDBC] Prepare Statement

So, I think that PreparedStatement should have a way at least case a String to an Array or a way to create a Array, because of conn.prepareStatement("SELECT myfunction('{1,2,3}')") is NOT very useful. Comment? Jie Liang -Original Message- From: Kris Jurka [mai

Re: [SQL] [JDBC] Prepare Statement

different arguments, should I consider performance iusse? Secondly, I assume the function should be a pre-compiled object stored on server side, doesn't it. Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 10:51 PM T

Re: [SQL] [JDBC] Prepare Statement

; St.setString(1,arr}; Result rs = st.executeQuery(); Then it will complaint when it run: Myfuntion(text) does not exist! Did I miss something?? Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 3:26 PM To: Jie Liang Cc: Tom La

Re: [SQL] [JDBC] Prepare Statement

Hmm, intersting. I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug could be fixed in later version. Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 3:26 PM To: Jie Liang Cc: Tom Lane; [EMAIL PROTECTED

Re: [SQL] [JDBC] Prepare Statement

nt("SELECT chr(?)"); Then my log file are same as yours.i.e. it use PREPARE and EXECUTE. So, I am getting confusion. I think CallableStatement is extended from PrepareStatement, it should have same behaviou. Any comment? Thanks. Jie Liang -Original Message- From: Kris

Re: [SQL] [JDBC] Prepare Statement

"parepare sql command" and five "execute sql commands", the log file shown what I typed, so I think it really used server side prepared object! Any comment? Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16,

Re: [SQL] [JDBC] Prepare Statement

: method setPrepareThreshold (int) location: interface org.postgresql.PGStatement pgstmt.setPrepareThreshold(3); I downloaded pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at http://jdbc.postgresql.org/download.html And had a try, I got same error msg. I use java 1.3.1, postgresql -7.4.2, Fr

Re: [SQL] Prepare Statement

Tom, Does java.sql.PreparedStatement do the same thing as SQL command prepare/execute ?? Which one should be used while I am using jdbc talking to postgresql? If using both, do them help the performance? Thanks. Jie Liang -Original Message- From: Jie Liang Sent: Monday, June 14

[SQL] Prepare Statement

the performance also. If I use java jdbc to connect postgresql database, which one I should use? Can I use both? Thanks. Jie Liang ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] [ADMIN] rules

Sorry, wrong question. -Original Message- From: Jie Liang Sent: Wednesday, May 19, 2004 10:20 AM To: Tom Lane Cc: postgres-list; [EMAIL PROTECTED] Subject: [ADMIN] rules According to the document of rule: CREATE RULE rulename AS ON delete TO mytablename DO ( delete from aaa where id

[SQL] rules

According to the document of rule: CREATE RULE rulename AS ON delete TO mytablename DO ( delete from aaa where id=OLD.id; Delete from bbb where id=OLD.id; Delete from ccc where id=OLD.id ); Should work, but it doesn't, what wrong with it? Even I use { .... } Jie

Re: [ADMIN] [SQL] \set

Thank you, Tom. Jie -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 4:06 PM To: Jie Liang Cc: Christian Kratzer; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [ADMIN] [SQL] \set "Jie Liang" <[EMAIL PROTECTED]> writes: &g

Re: [SQL] \set

able, but how could I use it in my SQL query? Thanks anyway. Jie Liang -Original Message- From: Christian Kratzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 3:13 AM To: Jie Liang Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] \set Hi, On Tue, 11 May 2004, J

[SQL] \set

art date'; It's not executable! Thanks. Jie Liang ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] \df

Thanks. Jie Liang -Original Message- From: Stefan Weiss [mailto:[EMAIL PROTECTED] Sent: Friday, May 07, 2004 4:40 PM To: [EMAIL PROTECTED] Subject: Re: [SQL] \df On Saturday, 08 May 2004 01:15, Jie Liang wrote: > What sql statement equal to \df function_name > I want to kn

[SQL] \df

Hi, What sql statement equal to \df function_name I want to know the result data type for a given function within plpgsql. Thanks. Jie Liang ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate

Re: [SQL] Procedure failing after upgrade

Are you using 7.4.x now? If so, I think the problem is condition: WHILE id_array[count_it] LOOP Change it to: WHILE id_array[count_it] NOTNULL LOOP Jie Liang -Original Message- From: patkins [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 6:32 AM To: [EMAIL

[SQL] string function -- replace needs a clear doc

rms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit urldb=# select replace('whatever%20sitis','si','SI'); replace --- whateve

Re: [SQL] pg_restore cannot restore an index

Sorry, it because I have another index with same indexname because pg_restore index fail. Thanks. Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 16, 2003 10:07 AM To: Jie Liang Cc: Tom Lane; [EMAIL PROTECTED] Subject: Re: [SQL

[SQL] pg_restore cannot restore an index

Last July, I pointed out this problem when I use v7.2.1, I got the answer that will be resolved in v7.3, however, I am using v7.3.1, pg_restore.c seems have no change in this section. So it still doesn't work. Jie Liang Jie Liang wrote: > I read the pg_restore.c source code,

Re: [SQL] plpgsql select into question

use 'INSERT INTO table1 ... SELECT ... ' stmt in your pgplsql. EXECUTE command is the way to execute a dynamic query in your function, read developer guide PL section of docs. Jie Liang -Original Message- From: J D [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 31, 2002

Re: [SQL] server terminated by a query in 7.3

Maybe you did a patch in localbuff.c to fix that vacuuming temp table? Jie liang -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 10:12 AM To: Jie Liang Cc: [EMAIL PROTECTED]; '[EMAIL PROTECTED]' Subject: Re: server terminated by

[SQL] server terminated by a query in 7.3

Tom, I've a perl script, which has been used for a long time, it works well, however, after I upgrade my postgresql from 7.2 to 7.3, one query always makes server terminated, could you give me a solution for it? Thanks. Jie Liang SELECT urlinfo.id,url,iprism_map,iprism_map_sg,level,domid,

[SQL] schedule of v7.3

Bruce, What is the schedule for releasing v7.3 stable? Oct? Thanks. Jie Liang ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] pg_restore --flag

I tried, I got same error msg. I even run as user postgres or myself, same same. My OS=FreeBSD4.3 DB=PostgreSQL7.2 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 4:25 PM To: Jie Liang Cc: 'Jan Wieck'; '[

[SQL] how pg_restore long form works?

su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable --dbname=mydb mydumpfile" error msg pg_restore: [archiver] could not open input file: No such file or directory I run it on 7.2.0 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Se

Re: [SQL] pg_restore cannot restore index

su postgres -c "/usr/local/pgsql/bin/pg_restore --table=mytable --dbname=mydb mydumpfile" error msg pg_restore: [archiver] could not open input file: No such file or directory I run it on 7.2.0 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Se

Re: [SQL] pg_restore cannot restore index

Same problem, did you test: pg_restore --index=aa --dbname=test /bjm/x ?? I didn't make it work, I may miss someting. Thanks! Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 7:51 AM To: Jie Liang Cc: 'Jan Wieck

[SQL] config postgresql.conf??

Hi, What parameter I should change in order to make postmaster taking CPU as much as possible? Maybe I should ask: how can I make big tables equijoin faster? I have a serveral tables that contain more 2.5 million records, I need to equijoin those tables often. Thanks! Jie Liang

Re: [SQL] Please, HELP! Why is the query plan so wrong???

please copy and paste the whole msg and your query! Note:what I mean ' join key' is the fields that link two tables. I don't think fb.b=0 is a join key! Jie Liang -Original Message- From: Dmitry Tkach [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 7:34 AM T

Re: [SQL] Please, HELP! Why is the query plan so wrong???

I believe that SQL will use the index of join 'key' when you join the tables if have any, in your query the (a,c) is the join key but d is not. Jie Liang -Original Message- From: Dmitry Tkach [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 11, 2002 3:51 PM To: [EMAIL

Re: [SQL] pg_restore cannot restore index

What parameter I should change in order to make postmaster taking CPU as much as possible? Maybe I should ask: how can I make big tables equijoin faster? I have a serveral tables that contain more 2.5 million records, I need to equijoin those tables often. Thanks! Jie Liang

Re: [SQL] pg_restore cannot restore index

On this point, I'd like to ask: 1. where I can download this new version? 2. does pg_restore --index=aa --dbname=test /bjm/x works also??? Because pg_restore --table=mytable --dbname=mydb mydumpfile doesn't work! I got same error msg. Jie Liang -Original Message- From: Bru

Re: [SQL] pg_restore cannot restore index

Thanks! But I did not make long form works also, is it: pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile ??? msg: pg_restore:[archiver] could open input file: No such file or directory could you give out a example of long form???? Thanks again. Jie

Re: [SQL] pg_restore cannot restore index

", 1, NULL, 'P'}, {"host", 1, NULL, 'h'}, {"ignore-version", 0, NULL, 'i'}, {"index", 1, NULL, 'I'}, So, -i may be mapped wrong, however, -I is illegal option. Thanks! Jie Liang ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] pg_restore cannot restore index

t;, 1, NULL, 'P'}, {"host", 1, NULL, 'h'}, {"ignore-version", 0, NULL, 'i'}, {"index", 1, NULL, 'I'}, So, -i may be mapped wrong, however, -I is illegal option. Thanks! Jie Liang

[SQL] postgres7.2.1 upgrading

er, no documentation says that. Anybody can tell me? Thanks! Jie Liang ---(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 th

Re: [SQL] pg_restore cannot restore function

work! I believe that the man page of pg_restore should be improved. Thanks. Jie Liang -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 11:14 AM To: Jie Liang Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTE

Re: [SQL] pg_restore cannot restore function

This is not the case, because those db on a same server, it's I dump data from one db and try restore one of it function into another db. Thanks for your response anyway. Jie Liang -Original Message- From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 03, 20

Re: [SQL] pg_restore cannot restore function

I am sure. I assume that pg_restore -t mytable -d mydb mydumpfile is same as pg_restore --table=mytable --dbname=mydb mydumpfile but it is not! the 2nd one will get: pg_restore: [archiver] could not open input file: No such file or directory Jie Liang -Original Message- From: Jan

[SQL] pg_restore cannot restore function

I use pg_dump -Fc mydb > dbf then I create anpther db by: createdb mydb2 I use pg_restore -P myfunction -d mydb2 dbf cannot restore myfunction into mydb2 why?? Jie Liang ---(end of broadcast)--- TIP 3: if posting/reading through Use

[SQL] FW: RESTORE A TABLE

pg_restore -aRt mytable -d dbname mytable but this takes too long to restore. 2. Is any way to disable unique index checking when loading, then enable the index after restored? Jie Liang ---(end of broadcast)--- TIP 3: if posting/reading through

Re: [SQL] plperl

As I know, plperl doesn't support that yet. plpgsql does, so does pltcl. 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, 5 Jun 2001, clayton cottingham wrote: &g

Re: [SQL] batch file

Hi, I am not sure what's your question. However, you can list a bunch of SQL statements in a text file: e.g. myfile.sql which contains: select * from tablename where update tablename set ... where ... . then, you can just run it like: psql -q dbname < myfile.sql Jie L

Re: [SQL] PL/Perl documentation ...

http://www.brasileiro.net/postgres/ 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, 2 May 2001, Adam Walczykiewicz wrote: > Is there available some more examples

[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

Re: [SQL] plpgsql

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, Ro

[SQL] plpgsql

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.ipin

[ADMIN] select ... for update in plpgsql

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.stberna

[SQL] Does pg_dump stable on v7.0

x27;::text) AND (urlpart('host'::text, (url)::"varchar") <> '*.net'::text)) AND (urlpart('host'::text, (url)::"varchar") <> '*.gov'::text)) AND (urlpart('host'::text, (url)::"varchar") <> '*.

[SQL] select statement inside a function: behavior bad

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 broadc

[SQL] using for rec inside a function: behavior very slow

ion, 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 PROT

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 t

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: > Accord

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

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/pg

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

Re: [SQL] pl/Perl

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 9212

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

[ADMIN] how to reload a function

ction 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 D

[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

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 li

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,

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

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

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

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

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

[SQL] sysfile cruptted?

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?? Ji

[SQL] Urgent help

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: >

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

Re: [SQL] Need your help

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

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 9212

Re: [SQL] Trouble with CREATE FUNCTION

he 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)

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:(85

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

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

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

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 Intern

Re: [SQL] How to create a type ?

al 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.c

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: >

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

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

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 In

Re: [SQL] What's wrong with this function

ashmi'); 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

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

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

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

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 PROT

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-48

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

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 morn

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: &g

Re: [SQL] How to change the ownership of the table?

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

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:

[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] 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 PROT

  1   2   >