Re: [SQL] [BUGS] session variable
On Tuesday 02 September 2003 16:40, you wrote:
> No problem to use a temp table in a trigger (which is local to the
> session), I do so myself. Still, session variables would be nice, making
> coding a little bit more comfortable.
(it would be very good if you implement session variables in PostgreSQL.)
The first problem using temp table is:
CREATE TABLE locals (name text, value text);
CREATE OR REPLACE FUNCTION test_locals_access() RETURNS text AS '
DECLARE i text;
BEGIN
SELECT value INTO i FROM locals WHERE name=''n1''
RETURN i;
END;
' LANGUAGE 'plpgsql';
SELECT test_locals_access() ;
column
-
CREATE TEMP TABLE locals (name text, value text);
INSERT INTO locals VALUES ('n1','xxx');
SELECT test_locals_access() ;
column
-
<=== the Function seing global table
SELECT value FROM locals WHERE name='n1';
value
-
xxx
the second problem may be resolved with your advise.
look:
i want to log operations on the data in some tables.
so i declared sufficient triggers which write to the log-table.
and now i want to mark each log-record with the "operator_id"
(e.g. to log who made an update)
what possible ways are there ?
if i use temp table to inform the triggers about "operator_id"
then i have problems in manual updates of these tables
(temp table in my session needed too)
---(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_type and type names
hello developers, another question i have to ask: may the type names changes in the future releases? may the type oids changes...? I need look-up system catalog for a type of a field, so i need a constancy of some kind :-) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] recursive sql
Good day On Friday 05 September 2003 21:41, you wrote: > can anyone recommend a good reference source for doing recursive sql on > postgresql? i want to do something similar to a BOM expansion. (i.e. i need > to traverse a self-referencing table that stores a tree structure and > answer a question like "Get me A and all of A's descendents") "recursive queries" are much slower than queries to a nested-tree. please find something readable on subject "nested-tree" or ask me to send you this. You'll see that the maintaining of a nested-tree is covered by its good profit. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] how to call a function with row-type arg
hi how to call a function with a row_type arg ?? that is the question CREATE FUNCTION foo(tablename) returns int . SELECT foo ( ??? ); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] auto_increment
On Saturday 20 September 2003 10:23, you wrote: > On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote: > > How to Create auto_increment field in PostreSQL. > > Can I create them using Trigger. > > Use the SERIAL datatype. See also the functions nextval(), currval() > and setval(). I believe it is better to EXPLICITLY declare a SEQUENCE and set the default value nextval() to the field. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Reg: Firing Trigger when a particular column value get changed
On Sunday 21 September 2003 02:38, you wrote: > Thilak babu wrote: > > I have a scnerio as to fire a trigger when i update a particular column > > in a table. Please do help me out in getting thro this. > > The trigger function can use logic to exclude cases where a particular > column does not change. For example: > > CREATE FUNCTION "column_update" () RETURNS TRIGGER AS ' >BEGIN > IF ( NEW.column <> OLD.column ) THEN >do-your-stuff-here; > END IF; >RETURN NEW; > END; ' LANGUAGE 'plpgsql'; this trigger will not execute "your-stuff-here" if NEW.column or OLD.column will be null. you need to add this case to the logic statement in the "IF" (NEW.column <> OLD.column) OR (NEW.column IS NULL <> OLD.column. IS NULL) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Large Objects and Bytea
Hi, Friends ! On Thursday 25 September 2003 08:11, you wrote: > I am migrating MS SQL DB to Postgres DB. I have tables with columns of data > type 'Image' in the MS SQL database. IF I choose 'bytea' datatype, I am > afraid it may lead to poor performance of the database (which I read from > the manual). In this case what is the best data type to use. I am using TEXT fields to store images and pdfs. There is one "feature": ZERO bytes causes interruption of input and output. so I am translating Zeroes to '#0' and '#' to '#1' (it is similar to work with bytea) I am really not sure that's the best, but usefull. BTW, i ask Developers: can you remove this "feature" of ZEROES in a TEXT ? AFAIK the TEXT-type itself allows ANY bytes to store ! (its length representet explisitly in 4 bytes) The "feature" obviosly grown from C strings.. ---(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 interval
On Friday 26 September 2003 09:18, you wrote: > I am looking for a way to convert an interval into a number of days ( > integer); > > In sybase I would use : > > days(today()-r_expire) SELECT now()::DATE - '1900-12-10'::DATE; ?column? 37545 SELECT now()-'1900-12-10'::timestamp; ?column? 37545 days 06:10:54.2021608353 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL Syntax problem
> I've got a problem in porting the following select statement from Oracle to > Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the > where clause: (+) > I don't know what these characters mean and how I can transform these into > PostgreSql Syntax. > > > select... > from auswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f > where k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp > and a.ask_id = f.ask_id(+) This (+) means JOIN e.g. (+)-marked equations used as a joining condition To translate it to PGSQL syntax simply remove (+) :-) (only one thing i forgot: isn't it OUTER JOIN?...) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] privileges
Hi all can anyone give me a link to a Reference manual which describes all privileges on any DB object and it's meaning :-) thnx. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] see a current query
Hello i'am logged in as superuser (pgsql) trying to SELECT * FROM pg_stat_activity; and seeing NULLs instead of current_query column& What is it? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] bug in working with TEXT constants ?
Good day.
AFAIK PostgreSQL provides the type TEXT with 4-byte prefix length
which is distinct to C's zero-terminated (char *)
That's very good.
Then I expect natural possibility to store texts having zero characters.
try
SELECT 'abc\0de';
SELECT length('abc\0de');
or insert such a value into another table and then select
you'll see a classical result just like you are using (char *).
i suppose it is a little bug deep inside like using a memcpy() or such...
Am i wrong?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] unescaped output of bytea
Good day Guru
I thought on output to input relation in general.
Manual says about scalar types that input and output functions should be
inverse to each other, because of dump problems (if output function prints
a value not in a format that input function waiting for)
That's the geat problem i thought. If so then an external representation
could not be deffer of SQL-constant representation (clear?)
Very common problem it is.
Most fields in this case should be manually (application level) transformed
both directions.
Then I try the experiment with text constant and text field dump:
INSERT INTO ttt values ('a\\b\'c''');
SELECT * FROM ttt;
fff
a\b'c'
I see the output not inverse to the input.
then I DUMP the table ttt and see into the dump file:
INSERT INTO ttt VALUES ('a\\b\'c''');
THAT'S IT !!! Dump DIFFERS to plain output !
the same result with escaping apostroph we could see in bytea type.
this GOOD, VERY GOOD fact allows us to have unescaped output of bytea
without problems with a dump.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] varlena versus null-terminated strings
Hi Developers. I think the problem is the input/output functions pass CSTRING -- not varlena. this waste all profits of using varlena storage cause values are not transparently passed. then application forced to convert them reverse. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] a question to developers
Hello Developers what had drove you to use CSTRING type for input argument of postgres-type input convertion functions (in general) and for output of postgres-type output conversion functions ? thanx ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] question to developers
Hello Developers what had drove you to use CSTRING type for input argument of postgres-type input convertion functions (in general) and for output of postgres-type output conversion functions ? thanx ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] DROP TRIGGER
hi all again i have little complex database was used in tests of a program in ordinal way (no manual expirements with pg_catalog have done) now the database seems damaged in strange manner: Postgres cannot drop nor create some triggers (he was thinking about an hour on the query then i have cancelled) The undroppable and uncreateable triggers are all on the one table of the DB ANY OTHER triggers looks fine (drop and create) VACUUM FULL VERBOSE ANALYZE; drove the Postgres to nirvana too with output: bla-bla-bla some table pf pg_catalog INFO: --Relation pg_catalog.pg_type-- INFO: Pages 7: Changed 0, reaped 1, Empty 0, New 0; Tup 375: Vac 0, Keep/VTL 0/0, UnUsed 10, MinLen 144, MaxLen 144; Re-using: Free/Avail. Space 1664/1472; EndEmpty/Avail. Pages 0/1. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Index pg_type_oid_index: Pages 2; Tuples 375: Deleted 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Index pg_type_typname_nsp_index: Pages 8; Tuples 375: Deleted 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Rel pg_type: Pages: 7 --> 7; Tuple(s) moved: 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Analyzing pg_catalog.pg_type that's all. I will not drop this DB and waiting for your reply. Dear developers if you suspect a bug, i would gladly provide any info on your request even dump. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] cascade delete
hi why a cascade delete is so slow ? pgsql 7.3.3 ---(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] cascade delete
> Check if it is using indexes. > 7.3 seems to ignore them somethimes, try upgrading to 7.4 where index use > is apparently improved. good, i will upgrade anyway but how can i check index usage when DELETE from table1; ? EXPLAIN tells me only "seq scan on table1" when many other tables involved in this DELETE by foreign keys ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Getting the week of a date
On Monday 16 February 2004 15:10, you wrote:
>
> Any idea on how to find the 3 rd Wednesday of any given month.
SELECT
1-(to_char(date_trunc('month', now()::timestamp),'D'))::INT2 + 7*3-3
replace now with any date and you'll the the day number of a third Wed in that
month.
7 is a constant (factor 3 is a desired week number)
-3 is number of days to step back from sunday to a desired day of week
(-3 stands for Wed)
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Getting the week of a date
EXCUSE ME, GUYS !
i forgot to add one monome:
7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
which is stands for skip a first week of month in case it is not consist Wed
finally the select will be similar the following
SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
(to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
4 - is a number of Wed in a week (in postgresql numeration)
---(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] bytea or blobs?
On Tuesday 17 February 2004 18:08, you wrote: > I'd recommend to let the application convert the binary > data to and from base64, Don't, please don't ! Since you have the good bytea rule to convert so called "binary" data into so called "text". You have no need another encoding at all. Generally, the problem is to represent zero (0x00) when input/output. Any other byte might be stored, dumped, input, output without any problem. Then why to avoid 8bit chars ? Bytea notation rule completely resolve the problem of zeroes. (and also apostrophes :-) naturally) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] perfomance question
hello. what are perfomance difference bitween a) update t1 set f1 = 'x', f2 = 'y'; b) update t1 set f1 = 'x', f2 = f2; c) update t1 set f1 = 'x'; ? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] perfomance question
On Thursday 18 March 2004 21:38, you wrote: > Sad, > > > what are perfomance difference bitween > > a) update t1 set f1 = 'x', f2 = 'y'; > > b) update t1 set f1 = 'x', f2 = f2; > > c) update t1 set f1 = 'x'; > > ? > > Not a lot. Why don't you try it? Really ! why ? :-) I forgot to ask the second question: How these updates affect a data storage ? (does (b) cause physical update of f2 ?) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] type conversions
hi SELECT 'tbl'::regclass; works fine SELECT 'tbl'::text::regclass; says cannot convert type text to regclass what to do ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] a wierd query
> i require the dictinct values from (visualizing each column > result as a set) the union of the two columns select distinct a as F from table union select distinct b as F from table; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] a wierd query
On Thursday 13 May 2004 19:27, you wrote: > sad wrote: > > select distinct a as F from table > > union > > select distinct b as F from table; > > Note that UNION only returns the unique values of the union > You can get repeated values by using UNION ALL. read the original problem look at the DISTINCT clause in my query and think again ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] a wierd query
> I forgot one situation: > > if I run the query like so: > select distinct on (task_id, begindate) task_id, workhour_id, > begindate as date from workhour UNION > select distinct on (task_id, enddate) task_id, workhour_id, enddate > as date from workhour I get yet another value: 2961 rows. > > So I got 3 different result sets for 3 different ways to run the query. > Even in this last case the UNION doesn't seem to only return unique values, > and I will still need the top-level select. if we suppose this situation possible to program in SQL it causes data-loss in query (i mean unpredictable query result: if you have two records (f=1,b=2),(f=1,b=3) "distinct ON f" makes a value of b meaningless) So you MUST NOT select that way P.S. This situation means: you have wrong data structure. ---(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] The PostgreSQL
Dear developers, many tricks were discussed here, and many feature requests generated. Every discussed problem ALREADY have solution in the current version of the SQL. Most of the problems are forced by incorrect design of a database ! I ask you: do not burden the PostgreSQL with features !!! Please !!! So well known Oracle is now sinking in the ocean of own features. Thnx. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] type regclass casting
hello look select * from pg_class where oid = 'sometablename'::regclass; (1 row) select * from pg_class where oid = 'sometablename'::text::regclass; ERROR can not cast TEXT to regclass What does this mean ? version 7.3.3 please try it on 7.4.x ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] type regclass casting
hello select * from pg_class where oid = 'sometablename'::regclass; (1 row) select * from pg_class where oid = 'sometablename'::text::regclass; ERROR can not cast TEXT to regclass What does this mean ? version 7.3.3 please try it on 7.4.x ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] empty string casting to typed value
Hello It is clear that '' is a bad integer or timestamp representation but during the user input NULLs are usually represented with empty strings sometimes bunch of 'if empty' instructions grows huge (and in case of casting to timestamp apostrophes make sense) Why you prohibit casting ''::int to NULL ? What would you advice ? thnx ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] casting int to bit
PLEASE NOTE : select 1::int8::bit(64); 0001 select 1::int4::bit(64); 0001 select 1::int2::bit(64); ERROR: Cannot cast type smallint to bit the last is a great surprise for me ! ---(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] feature request
hello it might be stupid... sometimes i am starving UPDATE OR INSERT command thnx ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] feature request ?
Hello since BOOL expression has three possible values: TRUE,FALSE,NULL plpgsql IF control structure should have three alternate blocks: THEN,ELSE,NULL shouldn't it ? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
On Thursday 24 June 2004 09:32, Michael Glaesemann wrote: > On Jun 24, 2004, at 2:12 AM, Josh Berkus wrote: > > Sad, > > > >> since BOOL expression has three possible values: TRUE,FALSE,NULL > >> plpgsql IF control structure should have three alternate blocks: > >> THEN,ELSE,NULL > >> > >> shouldn't it ? > > > > No, why? > > > > How would you construct a tri-valued IF/THEN? Doesn't seem too > > likely to > > me, as well as being different from every other programming language in > > existance ... > > Creating a new control structure to do handle this seems odd. However, > one could easily have the same effect using a nested if. Using the > pl/pgsql ELSIF construct, it's pretty straightforward. > > IF foo IS NULL > THEN ... > ELSIF foo > THEN ... > ELSE ... > END IF; here the foo expression woll be executed twice ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] feature request ?
On Wednesday 23 June 2004 21:12, you wrote: > Sad, > > > since BOOL expression has three possible values: TRUE,FALSE,NULL > > plpgsql IF control structure should have three alternate blocks: > > THEN,ELSE,NULL > > > > shouldn't it ? > > No, why? > > How would you construct a tri-valued IF/THEN? Doesn't seem too likely > to me, as well as being different from every other programming language in > existance ... Three valued BOOLEAN is already different "from every other programming language in existance" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
> I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" > ? Or that SQL is not a "programming language" ? Who said wrong ? who said SQL ? I thougth _WHY_ the IF control structure has exactly two alternate blocks ? a BOOLEAN expression has exactly two possible values, that's why ! Well in plpgsql we have tri-valued BOOL Every programmer asks "how a NULL value treated?" Now you treat NULLs as false. That's your point, but why ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] feature request ?
...IF ELSEIF ELSE it's all clear but what about unequality of BOOL type possible value set and IF alternatives set ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
On Thursday 24 June 2004 14:32, Alexander M. Pravking wrote: > On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote: > > Now you treat NULLs as false. > > Nope. NULL is neither true, nor false. It's "unknown", or "undefined". > > fduch=# SELECT 1 WHERE NULL::boolean; > ?column? > -- > (0 rows) DAMN !! Alex ! read the thread before answering ! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] feature request ?
> If you were to add a NULL block you'd have to deal with things > like, if you only have a then and else, do you run the else on NULL or do > you do nothing? If you do nothing, what if you want the null and else to > be the same, do you add another way to specify that? If you do the else, > then the else stops making sense since it's sometimes false and sometimes > not true. it is only syntax problem. really we have more than one way to continue execution if one block is skipped so your are free to define IF's behavior any way, particularly the way it is defined now. two-blocks IF is oviously enough to code ANY algorythm but the three-blocks IF is more adequate to tri-valued BOOL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] feature request ?
> then lots of currently perfectly correct > programs break. If they're the same, then ELSE has different meanings > depending on whether NULL is specified, and that's generally bad from an > understanding the language standpoint. i've already thougth on this new control structure needed but the name of the IF is perfect %-) > In addition, either adding a FALSE and NULL or just a NULL still involves > looking at the rest of the IF semantics to make sure they make sense. How > do those interact with ELSIF blocks? that is because we used to two-valued BOOL... but anyway i see no clear way to generalize ELSEIF. may i suppose it was wrong to historically define IF as two-blocks control in plpgsql ? forget it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] feature request ?
> Very simply, a boolean may have to values: true or false. It's also > possible that it's not been set to anything (NULL). really ? what about (13 < NULL)::BOOL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] feature request ?
On Friday 25 June 2004 09:37, Rosser Schwarz wrote: > On Fri, 25 Jun 2004 08:16:47 +0400, sad <[EMAIL PROTECTED]> wrote: > > > Very simply, a boolean may have to values: true or false. It's also > > > possible that it's not been set to anything (NULL). > > > > really ? > > what about (13 < NULL)::BOOL > > Per the semantics of NULL, 13 is neither greater than nor less than > NULL. NULL is the *unknown* value; it's impossible to meaningfully > compare it to anything else. Try (NULL = NULL)::boolean. It's NULL, > also. READ THE THREAD BEFORE ANSWER ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] feature request ?
> sad wrote: > > On Friday 25 June 2004 09:37, Rosser Schwarz wrote: > >>On Fri, 25 Jun 2004 08:16:47 +0400, sad <[EMAIL PROTECTED]> wrote: > >>>>Very simply, a boolean may have to values: true or false. It's also > >>>>possible that it's not been set to anything (NULL). > >>> > >>>really ? > >>>what about (13 < NULL)::BOOL > >> > >>Per the semantics of NULL, 13 is neither greater than nor less than > >>NULL. NULL is the *unknown* value; it's impossible to meaningfully > >>compare it to anything else. Try (NULL = NULL)::boolean. It's NULL, > >>also. > > > > READ THE THREAD BEFORE ANSWER > > WHAT MAKES YOU THINK HE HASN'T? I had answered to the proposal to PROHIBIT NULL VALUES > The key point of argument, and where the problem is with your (13 < > NULL)::BOOL point is this: IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] locks and triggers. give me an advice please
Good day. often, I am turning triggers off and on to perform a mass operation on a table, and i am interested how should i care of another user operations. the scene is: table t1 with user defined triggers and many tables reference t1, (so FK triggers defined on t1) the operation i want to perform on t1 makes a great load to a server and have no use in triggers at all. the best way to perform this operation is to delete all records, modify, and insert them back without changing any adjuscent table. (this way takes a few seconds.) so i turn off triggers on t1 completely (updating pg_class.reltriggers) operate and turn on triggers on t1. it works fine. the question is: what should i do to prevent other users of data modification on the t1 and the adjuscent tables while triggers is off ? thnx. P.S. ...what about TEXT to REGCLASS casting ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] locks and triggers. give me an advice please
thnx. i try to sound the idea to ensure myself that you are right. > begin transaction; > lock t1 in access exclusive mode; > > Turn off triggers and do your updates. > (Note, "truncate t1" is faster than "delete from t1" followed by a > "vacuum full" and you might consider running "reindex table t1" after > your mass update or if appropriate drop your indexes, load the data, > then recreate them.) > > Re-establish triggers. > > commit; --end of transaction unlocks the table in case another user inserts a record into an adjuscent table with the value of reference field NOT IN t1. the constraint causes reading of t1 to look up FK value IN t1. so my EXCLUSIVE lock prevents even reading and this user operation will be queued. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] surrogate key or not?
Thnx, Josh (Byou are very helpful. (B (B> There are, in fact, three very good reasons to use surrogate keys, all of (B> which are strictly due to limitations of technology; that is, (B> implementation and performance issues, NOT business logic. They are: (B> (B> 1) Convenience: It's very annoying to have to refer to a 4-column foriegn (B> key whenever you do a join in queries or want to delete a record, as well (B> as tracking a 4-element composite in your client software. (B> (B> 2) Performance: INT and BIGINT data types are among the most compact and (B> efficient stored in most RDBMSs. So using anything else as a key would (B> likely result in a loss of performance on large-table joins. (B> (B> 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and (B> updates. Some RDBMSs do not support CASCADE, forcing the client software (B> to fix all the dependant rows. This means that DBAs are very reluctant to (B> use columns which change frequently as join keys. (B (B> Now, you're probably wondering "why does this guy regard surrogate keys as (B> a problem?" I'll tell you: I absolutely cannot count the number of "bad (B> databases" I've encountered which contained tables with a surrogate key, (B> and NO REAL KEY of any kind. This makes data normalization impossible, (B> and cleanup of the database becomes a labor-intensive process requiring (B> hand-examination of each row. (B (B (BThe surrogate keys is a real big problem. (BBut i want to add another two reasons to have a surrogate keys (B (B4) Replication: to identify an object of ANY type (record of any table (Bregardless to datamodel), to store lists of deleted or modified objects (B(regardless to datamodel) (B (B5) Making a primary key: if there is no real key at all. (B (Bthe sentence (5) is debatable. in theory every relation has a real key, but in (Bthe practice we have historical datamodels without PK !!! it is impossible (Bbut it exists. (BFor example: (BHere in Russia we have a lot of different but identically named streets within (Bone city. They has absoluetly identical attributes. Historically only human (Bnot machines work on that datamodel and they all used to call such streets by (Bname adding some spechial non-formal explainations, for example: (B"deliver this message please to the house 35 on the Green street, that is to (Bthe west of the center of the city." (B"deliver this message please to the house 12 on the Green street, that is (Bshortest of the all Green streets in the town." (BAnother exaple is a table of user messages: (Buser has a number of messages they have no mean attribute and also could be (Bidentical, so you force this user to numbering his messages, or number them (Byourself. (B (B (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
Re: [SQL] surrogate key or not?
On Wednesday 21 July 2004 14:58, Markus Bertheau wrote: > > 4) Replication: to identify an object of ANY type (record of any table > > regardless to datamodel), to store lists of deleted or modified objects > > (regardless to datamodel) > > That sounds like a datamodel on data that belongs to another data model, that's right, these surr. keys actually belong to a metadata model, but it is also useful to references of a data model. and they are used this way. > > 5) Making a primary key: if there is no real key at all. > > When there's no key at all, there can't be a surrogate key, as I > understand it. In such cases a generated unique number comes in handy, > and it's a real primary key and no surrogate key. ...right. in case (5) we construct another datamodel indeed. with an artifichial real key :-) P.S. r u from Russia ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] ? on announcement of 7.5
Hello can anyone comment the announcement of 7.5 about "nested transactions" ? doesn't the nesting hurt the matter of transaction ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] surrogate key or not?
I want to add some notes > create table diagnosis ( > pk serial primary key, > fk_patient integer > not null > references patient(pk) > on update cascade > on delete cascade, > narrative text > not null, > unique(fk_patient, narrative) > ); 1) a sequence generates INT8 values (in general) and you have INT4 field to refer to a serial field. 2) narrative TEXT is very bad to check uniquness becauce it is non-formal human-generated native-language text. so it contains missprints, it may be rephrased many ways with the same meaning. 3) afaik a diagnosis doesn't belong to a patient, it belongs to a History, and History is marked with a date and status and belongs to a patient. do not treat my words as The Truth. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] surrogate key or not?
hello > This reminds me of a project I worked on many years ago, I was pretty much > fresh out of university writing a system for a large medical practice - > itwas principally for accounting purposes. I made lots of suggestions like > Josh's, only to get replies like Karsten's. I the progammer wanted to > codify everything so as to enable data analysis (ie linking complaints and > diagnosis, etc) but the doctors wern't interested. They just wanted to > write free text comments. And the reason for it (as far as I can tell) is > the distinction between general practice and reseach (such as > epidemiology). So (GPs) are not so much searching for new knowlege in their > patients records, as applying the knowlege gained from research (done by > researchers) to treat individual patients. Here the situation quite similar, a customer dictate drives the practice far far from logic. EVERY database i had desined in few years have been turned into a container of textual MEMOs completely unstructured. (because of patches and makeups) USER DO NOT WANT TO TAKE CARE OF THEIR INPUT. i do not know how are you all programming, really hope you are in defferent circumstances. Here a customer itself is a user and itself is a [man who formulate a problem] then every stupid idea to simplify input immediately turns to an urgent official order to do. Sorry. That is my work. Thank you all again for very usefull discussion on Surrogate Keys. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] surrogate keys and replication.
Josh, I agree to treat this case as a (1) convinence. But it is still very cpecific, more than just simplifying FKs And you have said about general GUID problem. Let we disscuss this problem ? I hope to here good ideas from you again. Now I solve the GUID problem, with one sequence of IDs on the main server. The clients ask the server to lease some IDs via special (application-layer) protocol. Server remembers who and when and what IDs have took. (in terms of segments [a..b],[c..d]... etc) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] surrogate key or not?
On Sunday 08 August 2004 04:29, Kenneth Gonsalves wrote: > but why would anyone want to change the value of an autogenerated serial > row? because of catenating data from two or more databases. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] casting BOOL to somthng
hello why BOOL can not be casted to TEXT ...nevertheless BOOL has a textual (output) representation 't' and 'f' letters why not to use this fact to define cast to TEXT ? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] casting BOOL to somthng
you wrote: > you can use CREATE CAST to make your own cast from boolean to text. thnx it helps. and i am still desire to know _WHY_ there are no predefined cast for BOOL ? and at the same time there are predefined casts for INT and FLOAT.. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] casting BOOL to somthng
On Tuesday 31 August 2004 16:22, Geoffrey wrote: > sad wrote: > > you wrote: > >>you can use CREATE CAST to make your own cast from boolean to text. > > > > thnx it helps. > > > > and i am still desire to know _WHY_ there are no predefined cast for BOOL > > ? and at the same time there are predefined casts for INT and FLOAT.. > > I'd like to understand in what context you would find this useful. > Don't take me wrong please. I'm by no means a db expert, but I can't > see a purpose for such a cast. Can you provide a reasonable example of > such usage? Yes i can. look: CREATE TABLE t (a int, b text, c bool); SELECT 'the row is: a='||a::TEXT||' b='||b||' c='||c::TEXT FROM t; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] casting BOOL to somthng
On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote: > On Aug 31, 2004, at 8:24 PM, sad wrote: > > and i am still desire to know _WHY_ there are no predefined cast for > > BOOL ? > > and at the same time there are predefined casts for INT and FLOAT.. > > I think the main reason is what is the proper textual representation of > BOOLEAN? True, PostgreSQL returns 't' as a representation for the > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or > 'true' or other representations. Picking one is perhaps arbitrary. There are many (infinite number) of INT representations, "Picking one is perhaps arbitrary." But you poke one and using it. If some one wants another representation you ask him do define his own function and use it instead of cast. And you are right. In your system integers textully represented as you define. Just define one representation for boolean and leave the rest for user definition. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] casting BOOL to somthng
On Wednesday 01 September 2004 09:24, Stephan Szabo wrote: > On Wed, 1 Sep 2004, sad wrote: > > On Tuesday 31 August 2004 17:49, Michael Glaesemann wrote: > > > On Aug 31, 2004, at 8:24 PM, sad wrote: > > > > and i am still desire to know _WHY_ there are no predefined cast for > > > > BOOL ? > > > > and at the same time there are predefined casts for INT and > > > > FLOAT.. > > > > > > I think the main reason is what is the proper textual representation of > > > BOOLEAN? True, PostgreSQL returns 't' as a representation for the > > > BOOLEAN value TRUE, but some people might want it to return 'TRUE' or > > > 'true' or other representations. Picking one is perhaps arbitrary. > > > > There are many (infinite number) of INT representations, > > "Picking one is perhaps arbitrary." But you poke one and using it. > > There's a fairly accepted convention for integer representations. > There's no such convention for boolean representations. then why do you print its value on a screen ?! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] casting BOOL to somthng
On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote: > On Sep 1, 2004, at 2:41 PM, sad wrote: > > On Wednesday 01 September 2004 09:24, Stephan Szabo wrote: > >> There's a fairly accepted convention for integer representations. > >> There's no such convention for boolean representations. > > > > then why do you print its value on a screen ?! > > Perhaps because if you don't print *something* you can't see it? since you printed it you poke a convention (of casting to string) if you can print it on screen why not to print it in string? ---(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] casting BOOL to somthng
> There's a difference between an output function and a cast to text. > One gives you an external representation of the data for end use. The > other gives you an internal representation for manipulation. And at the same time 't'::TEXT can be casted to BOOL 't'::BOOL but reverse. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] casting UNKNOWN to REGCLASS
> select 't'::text::bool; > ERROR: cannot cast type text to boolean > > If you're thinking 't'::bool, that's something different. Ok i have nothing to opppose and by the way (!!!) why TEXT can not be casted to REGCLASS ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Isnumeric function?
On Friday 10 September 2004 04:20, Theo Galanakis wrote: > I was just thinking, wouldn't it be great if the pg community had a site > where anyone could contribute their generic functions, or request for a > particular function. i vote positive. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] libpq-fe: PQgetvalue() ?
hi
does PQgetvalue() allocate memory rof its result, it returns ?
the answer will help me in problem:
should i free some cstring_variable if
{ cstring_variable=PQgetvalue(pgresult_variable,0,0); }
and could i PQclear(pgresult_varible) while cstring_varible is in use.
thnx
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Howto turn an integer into an interval?
hello
SELECT ('3600'::int::abstime-'epoch'::abstime)::interval;
try to modify this idea to fit your purpose.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] help on a query
On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote: > A query that should get the job done is: > > SELECT registration_id > FROM registrations r > WHERE NOT EXISTS ( > SELECT 1 > FROM receipts > WHERE registration_id = r.registration_id > ); Don't, PLEASE, don't !!! drive this way : SELECT r.registration_id FROM registrations AS r LEFT OUTER JOIN receipts AS rec ON rec.registration_id = r.registration_id WHERE rec.registration_id IS NULL; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Offtopic: psql
Hi (B (BPostgres has a perfect tool - psql (Bwhat libraries did you (developers) use to develop psql console and (Bparticulary command-line editor. (B (Bthnx (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B (B http://archives.postgresql.org
Re: [SQL] CREATE TYPE
Hello i note something related to this discussion > create table mytmp(name myvarchar(10,"en_US")); i meant that "en_US" is a locale name, then it means natural language and also character encoding -- those both things are not a matter of SQL-TYPE at all. It is wrong to represent application specific data in SQL-TYPE itself. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] A transaction in transaction? Possible?
On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote: > I thought nested transactions are available in the new > release (8) coming up. how to commit/rollback them ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] tree structure photo gallery date quiery
On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: > Hi folks. > > I'm looking at the possibility of implementing a photo gallery for my > web site with a tree structure > How would I go about creating a view to show a) the number of photos in > a gallery and b) the timestamp of the most recent addition for a > gallery, so that it interrogates all sub-galleries? nested-tree helps you associate a numeric interval [l,r] with each record of a tree and let father interval include all its children intervals and brother intervals never intersect see the article http://sf.net/projects/redundantdb for detailed examples and templates ---(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] SET AUTOCOMMIT TO OFF
On Wednesday 01 December 2004 18:42, Tom Lane wrote: > Christoph Haller <[EMAIL PROTECTED]> writes: > > Please, could someone point me to the right list > > or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq. > > libpq does not have any support for that. Does this mean libpq calls always uncommited or commited ? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Single row tables
On Tuesday 11 January 2005 17:45, KÖPFERL Robert wrote: > Hi, > > with what constraint or how can I ensure that one of my tables has exact > one record or 0..1 records? A trigger procedure BEFORE INSERT would help you. And a column type with only one possible field value would help you if you define UNIQUE INDEX on a field of this (user-defined) type. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] currval() within one statement
A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled problem or to be given an exact pointer to documentation regarding the currval() behavior in the described situation, that i had. ---(end of broadcast)--- TIP 1: 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] currval() within one statement
Richard Huxton wrote: sad wrote: A. Kretschmer wrote: is it expected that the currval() changes its value between calls within one statement ? Conclusion, don't call nextval() within a TRIGGER, and insert either nextval() for the column or omit this column. I only note that i still want to discuss the titled problem or to be given an exact pointer to documentation regarding the currval() behavior in the described situation, that i had. Well, the page in the docs isn't hard to find - http://www.postgresql.org/docs/8.2/static/functions-sequence.html But surely it works exactly as you would expect it to. nextval(S) advances the sequence and returns the new value currval(S) returns the current value of sequence S, which is whatever the previous call to nextval(S) returned. In the even you haven't called nextval(S) then it is undefined. Then this is the question on the execution order of the statement INSERT...SELECT... What do you think should happen? I had expected all the currval() calls to be called before all the triggers fired. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Multiple postgresql functions in a single transaction
Robins Tharakan wrote: > > It can be done, but it depends on how you are generating the value in the > > first function. > > If you sequences though you may have to take care of reverting it > > yourself. Sequences had been constructed in this manner not to cause pain for users -- think of it. So i advise to avoid reverting sequences ANYWAY. Even if you think that this would not cause a problem. > > -- Forwarded message -- > > From: Jyoti Seth <[EMAIL PROTECTED]> > > Date: Feb 6, 2008 11:51 AM > > Subject: [SQL] Multiple postgresql functions in a single transaction > > To: pgsql-sql@postgresql.org > > > > > > Hi, > > > > I have two postgresql functions. One function is calling another function > > for certain value. I want that these two functions work under single > > transaction so that even if the value gets generated in the second > > function > > and the first function that calls the second function fails. Then the > > value > > generated in the second function should also roll back. > > > > Please let me know if we can execute two functions of postgresql in a > > single > > transaction. > > > > Thanks, > > Jyoti Seth > > > > > > > > > > > > > > ---(end of broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] very frustrating feature-bug
acc=> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) RETURNS usr AS $$ INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) RETURNING usr.*; $$ LANGUAGE sql SECURITY DEFINER; acc=> ERROR: return type mismatch in function declared to return usr DETAIL: Function's final statement must be a SELECT. CONTEXT: SQL function "add_user" SURPRISE :-) SURPRISE :-) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] very frustrating feature-bug
On 02/17/10 13:51, Jasen Betts wrote: On 2010-02-17, silly sad wrote: acc=> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) RETURNS usr AS $$ INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) RETURNING usr.*; $$ LANGUAGE sql SECURITY DEFINER; acc=> ERROR: return type mismatch in function declared to return usr DETAIL: Function's final statement must be a SELECT. CONTEXT: SQL function "add_user" SURPRISE :-) SURPRISE :-) SQL functions are inlined when invoked, and so must be valid subselects. rewrite it in plpgsql. thanx for advice. may i ask? when this feature will be fixed? (now i am using 8.3.9) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] what exactly is a query structure?
hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT * from get_noobs(); And we have the following error ERROR: structure of query does not match function result type -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] what exactly is a query structure?
On 02/26/10 10:19, A. Kretschmer wrote: In response to silly sad : hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT * from get_noobs(); And we have the following error ERROR: structure of query does not match function result type Wild guess: your table noob has an other structure as expected, in particular login and/or shop_pass are not TEXT. they are texts. if we substitute constant '*' with a text field or even a subselect, the error disappear. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] what exactly is a query structure?
On 02/26/10 09:50, silly sad wrote: hello. Postgresql 8.3.9 CREATE TYPE usr_secrets AS (login TEXT, pass TEXT, shop_pass TEXT); CREATE OR REPLACE FUNCTION get_noobs () RETURNS SETOF usr_secrets AS $$ BEGIN RETURN QUERY SELECT login, '*' as pass, shop_pass FROM noob; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT * from get_noobs(); And we have the following error ERROR: structure of query does not match function result type my own wild guess: string constant '*' is of type "unknown" -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] client_timezone to server_timezone and reverse
HELO i am looking for strange things: a timezone conversion AS transparent AS a charset encoding conversion are. for __example__ (not intending to override the current behavior) i want to see now()::timestampTZ always the same (the server side time with the timezone predefined firmly) and to see now()::timeztamp calculated according to the client_timezone setting whatever the user set it to. In addition to the current behavior it whould be NICE to cast timestamTZ to timestamp taking in account a shift between server and client time, if client specified his TZ. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Private functions
On 03/14/10 06:21, Jasen Betts wrote: On 2010-03-13, Gianvito Pio wrote: Hi all, is there a way to write a function that can only be called by another function but not directly using SELECT function_name ( )? not really. but there may be another way to get the effect you want. read the section SECURITY DEFINER and GRANT and REVOKE and CREATE USER -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Odd query behavior
On 03/12/10 18:41, Dan McFadyen wrote: Hello, I've come across an odd situation. I've had access to a database where a the following happens: " SELECT * FROM table WHERE name LIKE 'abc%' " returns 2 rows... but... " SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name LIKE 'abc%') " returns 0 rows... I am sorry if it was already spoken. SELECT name FROM table WHERE name LIKE 'abc%' EXPLAIN SELECT name FROM table WHERE name LIKE 'abc%' EXPLAIN SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name LIKE 'abc%') -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] list of all months
It looks like a procedural problem. I would solve it in plpgsql. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] I, nead to capture the IP number from the PC how is running the script ...
On 03/15/10 22:18, John Dizaro wrote: I, nead to capture the IP number from the PC how is running the script first of all you must tell us how your client is connected to your database? there are a lot of different variants, and the most common of them is not PG related at all while the connection is made via http. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
On 03/17/10 17:52, Ignacio Balcarce wrote: CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID @NEWID VARCHAR(20) OUTPUT AS SET @NEWID = ( SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') + CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1)) AS VARCHAR) + CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS VARCHAR) FROM THUBAN_SEQ WHERE SUBSTRING(SEQ_ID,1,8)= REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') ) INSERT INTO THUBAN_SEQ VALUES (@NEWID) SELECT @NEWID AS ITEM_ID; GO At a first glance it looks like an INSERT INTO thuban_seq(seq_id) VALUES (your_strange_string_processing( now() )) RETURNING seq_id; But i couldn't interpret your extremely strange string processing with dates. Please, FIRST OF ALL, get rid of this unnecessary brainfuck, use postgres date-time arithmetic and clarify the idea of this routine. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
On 03/17/10 17:52, Ignacio Balcarce wrote: -- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE Sorry, your field is not an atom => your database does not met a FIRST normal form. it needs normalization urgently. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL Developer accessing PostgreSQL
On 03/29/10 20:33, Snyder, James wrote: Hello, Is there a way to configure Oracle’s SQL Developer to access a PostgreSQL database? IMHO, no. And (even if it possible) it is completely useless, since the Postgresql has the "psql" program far better than any oracle tool and than all oracle tools together. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
single table. nested tree + ordinal parent reference. nests are calculated in a trigger on insert. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
P.S. almost foget, do not try any oracle-like "tree-jouns" or "special types" or such a crap. your problem as plain as to store a pair of integers (or numerics (i prefer)) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
On 04/07/10 11:00, Achilleas Mantzios wrote:
Column | Type| Modifiers
-+---+---
id | integer | not null default
nextval(('public.paintgentypes_id_seq'::text)::regclass)
name| text | not null
parents | integer[] |
The parents of any node to the root, i.e. the path of any node to the root are
depicted as
parents[0] : immediate parent
parents[1] : immediate parent of the above parent
.
parents[n] : root of the tree
what this schema gives?
(1) the parent branch in one select.
what else?
nothing.
compare it to a nested-tree
id | integer | NOT NULL
name| text | not null
parent | integer |
l | numeric
r | numeric
(1) parent branch in one select
(2) child subtree in one select
(it makes a sence!)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cascading sum in tree with CTE?
to select a whole subtree of a particular node of a tree. u have to modify the tree representation in one of the two ways (according to you fine-tuned needs) (1) store in the EACH node the PATH from root to this node (2) store (l,r) segment representing the INCLUSIONS of nodes into other nodes subtree (exactly as segments include each other) (2) i forgot the "official" name of this type of tree representation. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to max() make null as biggest value?
On 04/14/10 08:33, Feixiong Li wrote: Hi , guys , I am newbie for sql, I have a problem when using max() function, I need get null when there are null in the value list, or return the largest value as usual, who can do this? i.e. max([1,2,3,4,5]) => 5 max([1,2,3,4,5,null]) => null if u want a function, not an aggregate then u have the greatest(...) except it does not return null on null input (i was really surprised with this completely perverted behavior (very unusual for postgres), but it is a fact) if u want to cheat u may just coalesce() each input argument then nullif() a result of the greatest() function (if only u have enough space in a reference range to room the one special value instead of null) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?
On 04/26/10 04:12, Andreas wrote: Excel files > pgAdmin > Access. looks like a complete offtopic How can I find those broken UTF-8 characters? How can I get rid of them? iconv -c BUT u should not have those characters at all if one is occured it most probably an error AND u should get rid of this error itself -- not of its consequences. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SPAM]-D] Re: [SPAM]-D] [SQL] How to find broken UTF-8 characters ?
How can I get rid of them? iconv -c AFAIK iconv would translate on file system level but I would think that messed up a allready messed up Excel workmap even further. I'd be glad to handle csv, too. pg_dump | iconv -c | psql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] LIMIT problem
suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? May anyone rely on it? Or we have to avoid this non SQLish trick? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT problem
On 04/30/10 16:57, Nilesh Govindarajan wrote: On 04/30/2010 06:20 PM, silly sad wrote: suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? May anyone rely on it? Or we have to avoid this non SQLish trick? It will execute foo only once, and give only one row out of the n rows it returns. If I'm wrong please correct me. I did not asked how many times foo() would be executed. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LIMIT problem
On 05/01/10 03:58, Jasen Betts wrote: On 2010-04-30, silly sad wrote: suppose i request SELECT foo(t.x) FROM t LIMIT 1; Whither it DEFINED how many times foo() will be executed? foo will be executed repeatedly until it returns a result or all the rows in t are exhausted. May anyone rely on it? not sure Or we have to avoid this non SQLish trick? This will execute it once (or not at all where t has no rows) SELECT foo(x) FROM (SELECT x FROM t LIMIT 1) as bar; this subselect "isolation" looks like a safe way. Unlike LIMITed select looks unpredictable to me. Thanx for assistance :) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Schema design / joins
__Orgs__ id name __Seasons__ id org_id fk(orgs.id) name __Teams__ id season_id fk(seasons.id) name __TeamFees__ id team_id fk(teams.id) *org_id<--- (?put extra fk here to avoid many joins?) NO. instead of it use triggers before insert/update CREATE FUNCTION foo() RETURNS TRIGGER AS $$ BEGIN SELECT org_id INTO new.org_id FROM __seasons__ WHERE id=new.season_id; END; $$ LANGUAGE plpgsql; et cetera. AND now other way lead you to the future. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
On 05/11/10 18:26, Torsten Zühlsdorff wrote: Tom Lane schrieb: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= writes: NEW.revision := addContentRevision (OLD.content_id, OLD.revision); /* not working line, just a stub: EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; */ RETURN NULL; This seems like the hard way. Why don't you just RETURN NEW and let the normal insertion happen? The trigger catches an UPDATE, not an INSERT. I need the old and the new row, because this should emulate revision-control of the content. just set whatever value u want to the fields of the row NEW. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
