Re: [SQL] Create table in functions

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

Re: [SQL] Optimizing huge inserts/copy's

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

u 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. wri

Re: [SQL] Optimizing huge inserts/copy's

'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. > &

Re: [SQL] Re: Order by in stored functions

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

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

Re: [SQL] 7.0.2: Arrays

==>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 Steamb

Re: [SQL] Protection of tables by group and not by users

t; 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

Re: [SQL] Cascading Deletes

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

[SQL] Odd stuff

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

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

ter 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://m

Re: [SQL] Error with DISTINCT

x27;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

Re: [SQL] Division

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

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 In

Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp

rt 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 Yah

Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp

ery? > > 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 t

Re: [SQL] how to store a query, that results in a table

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

Re: [SQL] [GENERAL] Foreign Keys Help Delete!

te 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

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

Re: [SQL] Data Type precision

,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

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

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

Re: [SQL] if else query help

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

Re: [SQL] Standard syntax?

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

[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

[SQL] need help urgent

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

lobal", 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 ?

(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

[SQL] plpgsql

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

[SQL] select ... for update

[ 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 Inte

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

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

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

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

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 PRO

Re: [SQL] substring ..

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

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

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

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

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

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

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

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] "'" 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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

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

[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

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

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

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

[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

[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

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

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

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

[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

[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] 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") <> '*.

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

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

  1   2   >