[SQL] how to alter/drop check contraint?
the subject should be self-explanatory, but: i have table: create table a (b text check (length(b)<10)); and for some reason i want to drop this check or alter this to length(b)<20. how can i do so? or maybe using trigger in plpgsql will be better? how to make trigger which will stop insert or update when something occurs? depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
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
>
[SQL] How to set autocommit on/off
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 -
[SQL] plpgsql ?
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; ret_val text; BEGIN select tna into ret_val from a1 where pamt_no=a2; RETURN ret_val; END;' LANGUAGE 'plpgsql' error run " select tna into ret_val from a1 where pamt_no=a2;" help me ,thanks
[SQL] `~' operator and indices
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'; 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 alter/drop check contraint?
There's no good way currently to drop the check constraint really. You probably can do it by removing the row for the constraint from pg_relcheck and changing the pg_class row for the table to have the correct number in relchecks. In 7.1, you'd probably be able to add the check constraint using ALTER TABLE ADD CONSTRAINT, but before that adding the constraint would probably be difficult. You're probably best off dumping the table, changing the constraint and then restoring it. [If you don't have any important data and you like the idea of potentially causing yourself great deals of pain and suffering, it might be possible to change the 10 to 20 by directly editing the pg_relcheck row. I have not attempted to do this though, so I'm not sure it would work.] Stephan Szabo [EMAIL PROTECTED] On Wed, 20 Dec 2000, hubert depesz lubaczewski wrote: > the subject should be self-explanatory, but: > i have table: > create table a (b text check (length(b)<10)); > and for some reason i want to drop this check or alter this to length(b)<20. > how can i do so? > > or maybe using trigger in plpgsql will be better? > how to make trigger which will stop insert or update when something occurs?
Re: [SQL] plpgsql ?
> CREATE FUNCTION autono (text,text) RETURNS text AS ' > DECLARE > a1 ALIAS FOR $1; > a2 ALIAS FOR $1; > ret_val text; > BEGIN > select tna into ret_val from a1 where pamt_no=a2; > > RETURN ret_val; > END;' LANGUAGE 'plpgsql' > > > error run " select tna into ret_val from a1 where pamt_no=a2;" You cannot give a table name as an argument. You should write different SELECTs for different tables, like this: if a1 = ''dummy1'' then select tna into ret_val from dummy1 where pamt_no=a2; end if; if a1 = ''dummy2'' then select tna into ret_val from dummy2 where pamt_no=a2; end if; ... And so on. Unfortunately... :-) Zoltan
[SQL] sorting the text values as integers
Hi,
i have a table with some text fields filled with a data like
100,23
235,12
500
200
the same fields somethimes contains the values like
100x100x25
125x125x50
200x80x90
and so on.
the client requires that rows are sorted in ascending order
for the case there are a float values, i do:
SELECT ... ORDER BY float4(field)
for the case there a text values, i do:
SELECT ... ORDER BY int2(substring(field from 1 for position('x' in field)-1));
so i can sort them ascendingly at least by the first integer (before
'x' char). otherwise (simply "ORDER BY field") they were sorted as text
values - 100x100x30, 10x10x10, 400x400x30, 40x40x20, ...
now it sorted as i need - 10x10x10, 40x40x20, 100x100x30, 400x400x30 ...
it's almost fine, but...
now i need to combine that 2 cases, so i try (the field called m1):
SELECT ... ORDER BY (CASE WHEN position('x' in m1)>1 THEN int2(substring(m1 from 1
for position('x' in m1)-1)) ELSE float4(m1) END)
i never used CASE WHEN ... THEN ... ELSE ... END construct before,
& assume the above is errorneus by default.
--:)--
Best regards, Sandis
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] Don't understand creation statement's answer
On Mon, 18 Dec 2000 12:32, Thomas SMETS wrote: > Hi, > > In the long term I'm willing to construct a Little Java application that > runs a library. you might find http://www.koha.org interesting Library management on all free software. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
