Re: [SQL] LONG - Question on dealing w/ numerics

he problem is with the function. I think the problem is with your program logic, as the funciton just inserts a *single* journal line and updates the balance. How do you insert the 4 entries required by a full double-entry transfer as you described? Also, how about po

Re: [SQL] LONG - Question on dealing w/ numerics

to your function that produced the above mis-balance, and I can easily spot the problem for you. -Josh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] which will be faster? w/ or w/o indices

y exploits and one backup-and-restore bug that could make it difficult to recover your database from a backup file. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an

Re: [SQL] Automatic casting

Vicente, > if I try nodo_fecha_activ<= 104422680 then it gives me this error > ERROR: Unable to identify an operator '<=' for types 'numeric' and > 'double precision' This is a known problem that will be fixed in a later version of

Re: [SQL] PL/pgSQL question

. However, implementation is quite cumbersome, and I won't try to detail it here, particularly since that language and method have been obsolesced and would force you to re-write your code when you do upgrade. If this feature is important to you, I strongly recommend that you upgrade to 7.3.1 now.

Re: [SQL] conversi ms-sql7 vs postgresql 7.3

date a ficticious "totals" table with the sum of credits and debits for a particular account. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] PL/PGSQL EDITOR

://www.globecom.se/tora/) functions as a PL/pgSQL editor if you have the Oracle PL/SQL libraries installed. I haven't tried this, myself. I use KDE's Kate. Good luck! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)-

Re: [SQL] Passing arrays

s its elements to a comma-delimited TEXT variable. 2) Build up your query as a dynamic string. 3) Do your query loop as a FOR record IN EXECUTE dynamic_query_string -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP

Re: [SQL] rownum

Chester, > sorry about this - braindead and cannot find in doc. what's pg's > rownum pseudo-column or function name that returns the record number of > a set? There isn't one, unless there's something in /contrib that you can build. -- Josh Berkus Aglio Dat

Re: [SQL] SQL Functions vs PL/PgSQL

omething I don't, I do not believe that PL/pgSQL stores execution plans for functions. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister co

Re: [SQL] Format Function

T; As a simple formatting function. For that matter, it would be the work of a weekend for someone to write a function in PL/Perl which would take a format mask and apply it to any text string. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broa

Re: [SQL] Drop temporary table only if it exists

jects were created successfully. I've personally written several such routines. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] PL/PGSQL EDITOR

ually, Shane just sent me the XML file, and I got it to work with KDE 3.0.3. If anyone can think of an appropriate place in the suite of PostgreSQL sites, I'll post a copy of it there. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)-

Re: [SQL] problem on truncate for v.7.3.2

ion, this could cause part of the function to be committed while the rest failed, or even cause a fatal error. As such, these statements have been deliberately disabled within PL/pgSQL and SQL functions. -- Josh Berkus Aglio Database Solutions San Francisco --

Re: [SQL] 7.3 "group by" issue

Dan, Chad, > I see the distinction you are making. > > Maybe Tom or Josh could throw out a better answer, but I think that youve > called it one thing in your select and tried to group by it using a > syntaticly different name. This looks like a bug to me. Please write it up

Re: [SQL] 7.3 "group by" issue

ation issue, then I'm with you. If you're saying its a SQL theory issue, though, I don't agree at all. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] PL/PGSQL EDITOR

Folks, One more note on the PostgreSQL SQL highlighting mode for Kate: Shane Wright, the author, has asked for feedback. So if you use it, please send feedback and requests to me and I'll forward them. -- -Josh Berkus Aglio Database Solutions San Francisco --

Re: [SQL] Help with query involving aggregation and joining.

| Maths| 2002-04-30 > 1 | 101 | Physics | 2002-01-20 Easy: SELECT id, courseid, name, max(submission) as submission FROM history JOIN courses ON history.courseid = course.id GROUP BY id, courseid, name ORDER BY name And as such, I suspect that your real case is more complicated than the

Re: [SQL] [BUGS] 7.3 GROUP BY differs from 7.2

That we add a warning in the 7.3 release notes about the breaking of backward compatibility. Thoughts? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] syntax question

James, > but thats what: > rec record > select into rec id from table; > return rec.id > > does > > my question was can i do this with a query built inside a string? No. That's what I was talking about. You have to use the loop. -- Josh Berkus Aglio Dat

Re: [SQL] syntax question

Jeff, > I think Josh meant to say you can't select the results of a *dynamically > constructed* query without a loop --- that is, you need FOR ... EXECUTE. > A plain EXECUTE doesn't support plpgsql's notion of SELECT INTO. That's correct. See the rest of the t

Re: [SQL] indexing

scan is faster than an index scan. > Pps > When indexing if searching tables is more important than concurrency - which type of index is best? You want to use a B-tree index for anything other than statistical and/or geometic data. You are unlikely to need any other kind of index. -- -Jos

Re: [SQL] Denormalizing during select

xt) FROM a JOIN b on a.id = b.a_id GROUP BY a.id, a.col1, a.col2 The only drawback of this approach is that you cannot order the items in the list, but it is *much* faster than the function method that Jeff outlined. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Return type of triger functions from OPAQUE to TRIGGER in 7.3

f 7.3, where we could make use of 7.3-specific > features. We wanted it to be a gradual path. Where does OpenACS do its object creation? Just at setup/install time, or through the life of the program? If the former, I'd suggest having two seperate database install scripts ... one fo

Re: [SQL] Design Q.:logic in app or db?

This desired feature would argue strongly in favor of putting as much business logic as possibly in your database in the form of views and rules. If users can bypass the interface and middleware, you cannot rely on it to enforce data integrity and access control. -- Josh Berkus [EMAIL PROTECTED

Re: [SQL] Arrays Or Loop

ou want to test this with arrays, you will have to use another language, such as C or Python. > 4. Lastly if i use views will they help in fas execution No. If you want fast execution, try writing the procedure in C. -- Josh Berkus Aglio Database Solutions San Fr

Re: [SQL] Cursors and backwards scans and SCROLL

PostgreSQL. On the other hand, I don't use cursors much in Postgres, so I'm kind of a priest doing marriage counselling as far as that's concerned. PL/pgSQL's "FOR record IN query" is currently both easier and faster than cursors so I use that 90% of the time.

Re: [SQL] Diffcult query

, because your DB design is poorly normalized. My first suggestion would be to make some design changes to your schema. Is that possible? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading t

Re: [SQL] Does anyone use TO_CHAR(INTERVAL)?

mmittee made a few mistakes with DATE/TIME, they *are* the ANSI committee and PostgreSQL as a project is very firmly committed to standards. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked ou

Re: [SQL] can i make this sql query more efficiant?

ient for small data sets.I've generally found that case statements are slower than subselects for large data sets. YMMV. BTW, while it won't be faster, Joe Conway's crosstab function in /tablefunc does this kind of transformation. -- Josh Berkus

Re: [PERFORM] [SQL] can i make this sql query more efficiant?

CASE statements to be slower. For your example, how do the statistics change if you increase the number of levels to 15 and put an index on them? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you chec

Re: [SQL] To ListAdms: Is pgsql-sql operating?

Achilleus, > Is there any problem with [EMAIL PROTECTED] list? The mail server died on Tuesday. It's still recovering. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] Changing owner of function -- best method?

ruser, replacing the functions, and then making the db_owner a non-superuser again. BTW, is there a neater method to deal with this in 7.4? 7.2 and 7.3 have ALTER TABLE ... CHANGE OWNER, but other types of objects don't seem to have a CHANGE OWNER option. -- Josh Berkus Aglio Database Sol

Re: [SQL] [Fwd: SQL book]

Jodi, > I am looking to purchase a SQL book to use with our postgresql database. > Can anyone recommend a good one? See: http://techdocs.postgresql.org/techdocs/bookreviews.php (HEY EVERYONE ELSE: I could use some more book reviews. It's 100 words, it's not hard ... e-mai

Re: [SQL] little doubt

Eric, > Shoul I thank the guys who help me? > Or to post other message saying ´it worked well etc´? > or it will flood the lists? I suggest sending a thank-you directly to the people who helped you, but not to the list. -- Josh Berkus Aglio Database Solutions San

Re: [SQL] [Fwd: SQL book]

. But it does give you a good general syntax comparison. Jodi, SQL in a Nutshell is a reference for people who already know SQL ... not a primer. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you ch

Re: [SQL] CREATE TABLES AS looses constraints

of a set of tables first and when happy, > these are copied to the operational tables. I suggest using pg_dump, on the command line: pg_dump -T some_table my_database > some_table.pgdump; psql -U database_owner my_database_mirror < some_table.pgdump; This will copy triggers and indexe

Re: [SQL] SQL Help

ole approach to designing this application; it may be that you can simplify your queries by changing your table design. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] PostgreSQL and industry

The biggest I can mention is, of course, that the .ORG web registry runs on PostgreSQL. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] generic return for functions

configuration as SS. It will be interested to > compare them then. That's a very nice testimonial! Thanks. BTW, you will probably wish to join the PGSQL-Performance mailing list to make sure that you can tune your PostgreSQL database properly. -- Josh Berkus Aglio Data

Re: [SQL] simulating partial fkeys..

RT, UPDATE ON cases, and FOR UPDATE, DELETE on status. The triggers on status would be annoyingly long. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Change owner of function in 7.2.4?

hing them back. Thoughts? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Elegant SQL solution:

rsor of the totals and outputs that. However, I think your first method is likely to be the fastest and easiest to maintain. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Record size

proved worlds since 7.0; it's quite possible that optimization workarounds you make in 7.0 will actually harm performance in 7.3 or 7.4. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] help

ossibly plus schema for the underlying tables (including indexes), or we can't help you. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] select date range?

format. Try re-formatting the date to '2003-05-12' -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Catching DML exceptions in PL/pgSQL

? Unfortunately, we seem to still lack a dedicated PL/pgSQL source developer on the project. Know anybody? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?

Re: [SQL] Object-Relational table design question

e.id = webhosting.service LEFT OUTER JOIN web_advanced ON webhosting.id = web_advanced.webhosting Which would give you all customer, service, and basic hosting details, plus advanced hosting details of there are any. -- Josh Berkus Aglio Database Solutions San Francisco ---

Re: [SQL] disabling triggers

e. No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe

Re: [SQL] join syntax

ery writer is not ideal. That is, the "FROM table, table, table WHERE expression, expression" syntax gives the parser a freer hand to choose the fastest execution method. Of course, on a very small database that typically makes litte difference. -- -Josh Berkus Aglio Database Soluti

Re: [SQL] disabling triggers

pproach > would work in principle ? Yes. As I said, I've used it before. An additional safeguard you can use is enclosing everything in a transaction, that is: BEGIN disable triggers load data enable triggers END -- -Josh Berkus Aglio Database Solutions San Francisco ---

Re: [SQL] Object-Relational table design question

ke place. Yeah, that's a very good approach. I use it for any client where they need to be able to add new "attributes" and services after the system is built. It also works for other things ... for example, a "skills" list for an HR database. -- Josh Berkus Aglio

Re: [SQL] Database design - AGAIN

HP - I haven't decided yet. See what I said about expediency above. My web guru, who is an expert in both PHP and Cold Fusion, would be delighted to never use Cold Fusion again. So that's one expert opinion. And don't forget the license fees. -- Josh Berkus Aglio Database Solu

Re: [SQL] Database design - AGAIN

r saw the value in UML, mainly because of the confusing terminology. But if it works for you, go for it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] cleaning up useless pl/pgsql functions

e there are any builtins/contrib stuff that uses plpgsql. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] help with "delete joins"

D bar.b = foo.b AND bar.c = foo.c ); -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] LEAST and GREATEST functions?

rt of > a future version Postgres? Um, what's wrong with MAX and MIN, exactly? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] LEAST and GREATEST functions?

2, now() ); With an "Operator is not defined" error, hey? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] LEAST and GREATEST functions?

tation in 7.3. Actually, we did ... that was one of 3-4 "killer features" for 7.3 -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command

Re: [SQL] LEAST and GREATEST functions?

I > am running v7.3.2. Just do a: LEAST(my_timestamp_field, TO_TIMESTAMP('2003-07-01 12:34:56', '-MM-DD HH24:MI:SS')::TIMESTAMP WITHOUT TIME ZONE) with and without time zone are effectively seperate data types with easy casting

Re: [SQL] disabling triggers

bles to find out the trigger definintion (you'll need pg_trigger, pg_proc, and pg_type) 2) generate a script to restore all the triggers to be used later; 3) drop all the triggers Of course, setting reltriggers=0 is probably a lot easier. -- -Josh Berkus Aglio Database Solutions San F

Re: [SQL] OR vs UNION

above form would not benefit from being a union. For readability, you could use an IN() statement rather than a bunch of ORs ... this would not help performance, but would make your query easier to type/read. -- -Josh Berkus Aglio Database Solutions San Francisco ---

Re: [SQL] Table Partitioning and Rules

splitting stuff into 3 tables will not improve your performance ... quite the opposite. Change your database design. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once w

Re: [SQL] Table Partitioning and Rules

7;t it? Yes, it would. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] [HACKERS] plpgsql strangeness with select into

code, I can't tell for sure. However, I would guess that your "SELECT INTO" statement is querying data that has not yet been created; it's an FK record waiting on a deferred trigger, or you're using a BEFORE trigger and querying the record which has not y

Re: [SQL] (trigger function) -> ERROR: NEW used in non-rule query

And what's wrong with Perl? Other than the inability to write triggers with it? (We want to enable triggers in PL/perl, but that functionality isn't coming until at least 7.5). -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--

Re: [SQL] obtuse plpgsql function needs

Robert, > I'm starting to believe this is not possible, has anyone already done > it? :-) It sounds doable but you need more explicit examples; I can't quite tell what you're trying to do. -- Josh Berkus Aglio Database Solutions San Francisco ---

Re: [SQL] obtuse plpgsql function needs

e value of the column f1. This makes it impossible for me to understand which of the two you want. Try again? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] obtuse plpgsql function needs

Robert, > 2) would it be faster in pltcl? seems like it would if i didn't have to > do the catalog lookups, but is pltcl inherently faster anyways? Probably, yes. Execution of dynamic query strings in PL/pgSQL tends to be pretty slow. -- -Josh Berkus Aglio Database Solutions Sa

Re: [SQL] Problem using Subselect results

(SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c FROM (SELECT a, b FROM table2 WHERE b=1) my_ab; Although in the simplistic examples above there's not much reason to use a subselect at all, of course. -- Josh Berkus Aglio Database Solutions San Francisco ---

Re: [SQL] transactions

tion. Just in case, I'd suggest doing a VACUUM ANALYZE right before running your big transaction. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Problem using Subselect results

Not that the LIMIT 1 method can be used with all queries. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL P

Re: [SQL] Fwd: Bad Join moment - how is this happening?

an you do a \d addenda and post the results? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Fwd: Bad Join moment - how is this happening?

> I think you can turn this "feature" off in the config file in 7.3.x (haven't > checked this though) Nope, it's a 7.4 feature to turn it off in .conf. Look under the "compatibility" section (the last section) in 7.4's postgresql.conf. -- -Josh Berkus Aglio

Re: [SQL] function returning setof performance question

Postgres planner to "push down" the WHERE criteria into the view execution. I've been planning on testing the performance of SRFs vs. views myself for paginated result sets in a web application, but haven't gotten around to it since I can't get my www clients to upgrad

Re: [SQL] What day is it - when it isn't NOW()?

o_char version work for you. Alternately, SELECT EXTRACT(dow FROM '2003-08-04') will give you a numerical (0-6) day of the week. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs

s no equivalent in PL/SQL. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs

Guys, > I'm not an Oracle bunny but they seem to have something vaguely similar > to what we do; they call it "EXECUTE IMMEDIATE" and the concept is > described as "Dynamic SQL". Aha. I see it now; a pretty awful OO-package-style format. I don't thi

Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs

Bruce, > OK, so what should the TODO item be? Go with the simple and intuitive: EXECUTE query_var INTO record_var; -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire

Re: [SQL] Abort Transaction DP PK (again)

ture release, to have an exception-handling for all > client interfaces, like ODBC or JDBC? I'm not quite sure what you mean. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore y

Re: [SQL] Porting from PL/SQL to PLPGSQL

r INSTEAD OF INSERT/DELETE/UPDATE > for triggers in PostGreSQL. In Postgres, this is generally done through the RULES system instead of triggers. Please lookup CREATE RULE in the online docs. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadca

Re: [SQL] Order of triggers - totally lost

G - > Am I reading you right, and statement triggers don't work in 7.3? or some > aspect of the order of statement- and row-level triggers? Correct, they don't work in 7.3. -- -Josh Berkus Aglio Database Solutions San Francisco ---(

Re: [SQL] Order of triggers - totally lost

(we only use FOR EACH ROW triggers) These will not work until 7.4, and then there will be some limitations (which will hopefully go away in 7.5). -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have yo

Re: [SQL] lower/upper functions and strings in searches

ld be your problem; 'NM'::undefined == 'NM '::CHAR but 'NM'::TEXT != 'NM '::CHAR so casting everything to the desired type should fix the problem. and why are you using CHAR, anyway? -- -Josh Berkus Aglio Database Solutions San Francisco ---

[SQL] OFF-TOPIC: Richard Huxton, Please Contact Us!

ail. Please contact us. cc: to me at josh at postgresql.org just in case. Thanks! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Abort Transaction DP PK (again)

y, have a exception-handling model for procedure code. It's on the TODO list. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (se

Re: [SQL] Reverse pattern match.

beginning followed by at least 7 other digits. (Folks, please correct my regex code if it's bad!) The disadvantage to this approach is that it cannot be indexed. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Inheritance or no inheritance, there is a question

ly. Want the B group? SELECT A JOIN B Want the A group only? SELECT A EXCEPT B This is the "relational" way to approach the problem. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and

Re: [SQL] Inheritance or no inheritance, there is a question

eed to operate on these two tables. Is > it possible using a view to them as one table? Yes. In fact, by configuring PostgreSQL's RULE system, you can make the view updatable, insertable and deleteable as well. See the online docs under "CREATE RULE" and "Server-side Prog

Re: [SQL] problem with automatic altering of groups

an't substitute variables for object names. If you need to construct dynamic query strings, use PL/pgSQL and EXECUTE: sql_qry := ''ALTER GROUP '' || $group || '' ADD USER '' || $user; EXECUTE sql_query; -- -Josh Berkus Aglio Databa

[SQL] Delete denied?

e owner of the table (and the function). -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Delete denied?

rther update: I tried changing the owner of the function and table to no avail. Also tried dropping the FK. It seems to work if called by the owner of the database, but not otherwise. I'm really baffled ... I've written several hundred procedures for 7.2.4, and have never seen anyth

Re: [SQL] Case Insensitive comparison

owing as parameter or return type. Is it possible? i want to > create a function similar to NULLIF(). You can't, nor will you be able to -- in te future, some 7.4 functions will be able to *accept* any type, but they will still return a specific type. Instead, you need to create a s

Re: [SQL] Case Insensitive comparison

Use the standard coalesce(). NULLIF is the converse of COALESCE(). Any idea when you're going to overhaul the CookBook? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Capturing pgsql ERRORS/NOTICES to file

ur shell for more creative redirection. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Removing simliar elements from a set

What do you want, exactly? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Mystery function error

rn position(searchstr in srcstr); You're missing "END;". > ' > LANGUAGE 'plpgsql' VOLATILE; Also, the function is not VOLATILE. It's IMMUTABLE, and STRICT as well. -- Josh Berkus Aglio Database Solutions San Francisco ---(end

Re: [SQL] Mystery function error

Richard, > The goal is to have > > locate( stra, strb) = position(strb in stra) Also, this will run faster if you do it as a SQL function: CREATE FUNCTION locate ( text, text ) RETURNS INT AS ' SELECT POSITION($2, $1); ' LANGUAGE SQL IMMUTABLE STRICT; -- Josh Berkus Aglio

Re: [SQL] Mystery function error

Tom, > position()1 usec/call 1 usec/call > SQL func 1 usec/call 90 usec/call > plpgsql func 110 usec/call 100 usec/call Hmmm ... this does still seem to show that plpgsql is 10% slower in 7.4. Any idea why? -- Josh Berkus Aglio Database Solutions San

Re: [SQL] postgres index on ILIKE

ON table(lower(text_field)); Then, you make sure when querying to query the lower function: SELECT * FROM table WHERE lower(text_field) LIKE 'xxxyy%'; This will use the index wherever it improves execution. I suggest that you join the PGSQL-SQL mailing list for future questions of

<    2   3   4   5   6   7   8   9   >