Re: [SQL] installing pgaccess
Keith Wong writes: > Anybody know how to compile pgaccess from postgres source files? > It must be a configure option, but I can't find it. --with-tcl --with-x -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] transactions surrounding extension functions
Forest Wilkinson writes: > Does this mean that when I call a function I wrote, which is composed > of several queries, each of those queries will be executed in its own > transaction? No > Or, will the statement containing the function call be executed in its > own transaction, thereby including all the queries composing my > function in that same transaction? Yes -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] invoke an external shell script from a function
Chau, Artemis writes:
> Does anyone know how to invoke an external shell script from a function when
> it is called from a sql statement?
If it's a C function, use system("file").
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Transaction isolation level for plpgsql
Keith Wong writes: > Does anybody know the transaction isolation level default when a plpgsql > stored procedure is called? > Is it possible to set it? Or is this controlled by SPI? It's whatever the transaction that called the function was set to. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Re: [GENERAL] All function parameters become NULL if one is?
Joel Burton writes: > I have a function that takes two text parameters. If both of these > are non-null, everything works fine. However, if one of these two > parameters is null and one is not, the function receives both as null. Worse, no matter what the function does, the return value is also null. > Is this intentional? Is there a way around this? Wait for 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] createuser
Craig May writes: > createuser -adq username > > returns > > invalid option adq createuser -a -d -q -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Granting of permissions on tables
Saltsgaver, Scott writes: > GRANT ALL on to ; > > after running the command I lost permissions to the tables once I exited > psql. I had to run psql as the postgres user to correct the situation. > > Is this a bug or desired behavior? It's a bug. Fixed for 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Standard syntax?
Franz J Fortuny writes: > select col1, > case when col2 = true then > col3 > else > col4 > end as colw, > colM > where etc. > is this a "standard" or ANSI 9X compatible syntax Yes. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] problem with select where like ']'
Joseph Shraibman writes: > The only way I can find is to do a search on something, and select to > search in mailing lists. Then after the search returns click on a link, > and trucate the url to http://www.postgresql.org/mhonarc/ When in doubt, use geocrawler.com. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Alternate Database Locations
Brian C. Doyle writes: > Okay I am still doing something wrong here > > I set PGDATA2=/home/user1/database > export PGDATA2 > > then I start postmaster > postmaster -d PGDATA2 -i -p 5431 & postmaster -D $PGDATA -i -p 5431 >logfile 2>&1 and i get > > Can't create pid file: /usr/local/pgsql/data/postmaster.pid > Is another postmaster (pid: 10686) running? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Synonyms
Carl Shelbourne writes: > * Is there a way of creating synonyms of tables within one database in another > database in Postgres similar to the Informix CREATE SYNONYM? Nope. > * If not and related to the post on 2000-10-28 by Sivagami, is there a way, > within a select I can make a query from mulitple databases. ( the usual > DB.Table or DB:table or DB@table dont seem to work) Nope. It's not really planned either in case you wanted to ask. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Postgres 7.0.X and arrow keys
Antti Linno writes: > I installed new Mandrake 7.2 and was eager to try new postgres. So I > installed it and used pgsql. What surprised me was that the arrow keys > wouldn't work anymore as history, instead I get those ascii codes. You need to install the readline-devel RPM. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] plperl
Jie Liang writes: > "../../../src/Makefile.global", line 304: Need an operator > make: fatal errors encountered -- cannot continue > su-2.04# > > what I need to do? Use GNU make. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Decimal vs.Numeric vs. Int & type for OID
Nikhil G. Daddikar writes: > It is unclear to me what the real difference is and when to use what. > Any pointers would be greatly appreaciated. Integers are integers (uh...) Numeric is for exact fixed-point decimal numbers. Monetary amounts are a good example. Decimal is equivalent to numeric. The oid type should only be used as a foreign key to an oid column. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Documentation for CASE
Albert REINER writes: > I think that the documentation for CASE is not very clear: Maybe you will like this better (from upcoming 7.1): http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] trying to pattern match to a value contained in a column
Beth Gatewood writes: > So-If I had a table where I had LONG_NAME and ABBR as attributes. > > I want something like > > SELECT whatever FROM my_table WHERE long_name LIKE '%[the value of ABBR > in that row]%'; SELECT whatever FROM my_table a, my_table b WHERE a.long_name like (b.abbr || '%'); -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] MD5 use in PL/Perl
Marc Rassbach writes:
> Attempting to xlate to PL/Perl
>
> settle=# create function md5out3(varchar) returns varchar(32) as '
> settle'# use MD5;
> settle'# my $mdval = new MD5;
> settle'# my $result ;
> settle'# my $out;
> settle'# $mdval->add($_[0]);
> settle'# $result = $mdval->digest();
> settle'# $out= unpack("H*" , $result );
> settle'# return $out;'
> settle-# LANGUAGE 'plperl';
> CREATE
> settle=# select md5out3('fred');
> ERROR: creation of function failed : require trapped by operation mask at
> (eval 6) line 2.
You can't use external modules ("use", "require") for security reasons.
FWIW, if I were to write an MD5 function then I'd take one of the
implementations floating around (mhash, Kerberos, OpenSSL, RFC) and make a
C function wrapper around it.
Incidentally, someone has already done this for the upcoming 7.1 release,
but since the function call interface has changed the back port won't be
trivial.
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Rules
Peeter Smitt writes: > CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD > SELECT fun1(new); > > Thing is that backend gives this error. > ERROR: parser: parse error at or near ")" Try double quoting "new", since it's a reserved word. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Looking for comments
Thomas SMETS writes: > > ISBN's have a checkdigit; it would be sensible to provide a > function to be used in a CHECK constraint to ensure that the > ISBN is valid. > > > OK I'll start searching for it but haven't implemented yet In the PostgreSQL distribution there's a directory contrib/isbn_issn that defines a couple of data types that might help you. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] psql -f option
Graham Vickrage writes: > The documentation says -f enables some nice features such as error messages > with line numbers. It seems to me that this is half true i.e. it shows me > error messages, its doesn't however give me the associated line number in > the script. Works here: $ psql -f /etc/sysctl.conf psql:/etc/sysctl.conf:8: ERROR: parser: parse error at or near "#" It looks to me like you are using an older psql, perhaps from a previous installation. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Casting
Thomas SMETS writes: > I'm removing charaters from a String which should be numbers. > I then want to make calculations on these numbers (calculate the ISBN > number). (You might want to look into contrib/isbn_issn for an isbn type.) > Do I have to cast the char into int's before I can do the calulations. Depends on the calculation. I'd just try to see if it works. When in doubt add casts. > Also I looked in the User manual but could not find the modulo function > where is it ? 5 % 4 mod(5, 4) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] how to select a time frame on timestamp rows.
[EMAIL PROTECTED] writes: > today i was trying to perform a query on a database using a time stamp > field, i need to get all records which belong to year 2000, month 11, > is there any other way to doit, or is this the pgsql way? , actually > i'm using a query like this: > select User_Name from tbacct where acct_timestamp like '2000-11%' group > by User_Name; select user_name from tbacct where extract(month from acct_timestamp) = 11 ... (SQL compliant) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] single byte unsigned integer datatype
Joe Conway writes: > I was looking for a datatype to represent a single byte unsigned integer. > The closest thing I can find looking through the online manual is a one byte > char. Are there any side-effects of using a char datatype for this purpose? Yes, it won't store single byte unsigned integers, only single byte signed characters. > Is there a better datatype to use? smallint with a check constraint -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Re: [INTERFACES] outer join in PostgreSql
Mauricio Hipp Werner writes: > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? No symbol, just words. http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] select returns no line
Attila Kevei writes: > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd|user_exp > ---+--+---+ > (0 rows) > > goodwill=> select * from users where user_id=4; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) You probably started the postmaster with two different LC_COLLATE (locale sort order) settings. The setting that was active when you ran initdb must be kept when you run the postmaster. > goodwill=> update users set user_login=user_login where user_id=4; > UPDATE > 1 > > goodwill=>select * from users where user_login='test'; > user_id|user_login|user_passwd |user_exp > ---+--+-+ > 4|test |0PDv7a2EESjZo| > (1 row) Yep, the update will fix the corrupted index (at least to the extent that this particular case now works). -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] wrong query plan in 7.1beta3
Kovacs Zoltan writes: > There seems to be an optimizer problem in 7.1beta3. The query you can see > below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is > that an 'index scan' has been changed to a 'seq scan'. Details: > Subquery Scan sd_user_grant (cost=38.68..38.85 rows=1 width=61) > -> Aggregate (cost=38.68..38.85 rows=1 width=61) > -> Group (cost=38.68..38.73 rows=10 width=61) > -> Sort (cost=38.68..38.68 rows=10 width=61) > -> Nested Loop (cost=0.00..38.51 rows=10 width=61) > -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 >width=32) > -> Seq Scan on sd_grant (cost=0.00..20.00 rows=1000 >width=29) You haven't VACUUM ANALYZE'd the sd_grant table. Therefore the row estimate is way off (1000 vs 6) and thus a sequential scan is (correctly) thought to be faster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] binary operators
Frederic Metoz writes: > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? in 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Suggestion for psql: --file -
Albert REINER writes: > wouldn't it be a good idea (and if it is, I am not sure what list to > post it to) to have psql's option -f / --file take "-" for stdin, as > many programs do? Seems reasonable. > P.S: BTW, the man page (7.0.2) of psql is not very clear: it took me > some experimentation to find out that you have to do "\set VARIABLE" > interactively or give "--set VARIABLE=" to set a variable that does > not take a value. Suggested new wording? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] 7.0.2-docs: textpos -> strpos
Albert REINER writes: > in the 7.0.2-docs I find the function textpos: > However, in psql it seems one has to use strpos: textpos() was removed from the 7.1 documentation. position() is the SQL function, I think strpos() is from Oracle. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Suggestion for psql: --file -
Albert REINER writes: > > Suggested new wording? > > What about: Works for me. Thanks. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] SQL Join - MySQL/PostgreSQL difference?
Brice Ruth writes: > SELECT > a.Number, > a.Code, > a.Text > FROM > b, > a > WHERE > (b.Id = a.Id) AND > (VersionId = 'key1') AND > (Category = 'key2') AND > (b.d_Id = 'key3') > ORDER BY > a.Number; > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > So my question is this: would this query operate differently in MySQL > than in PostgreSQL? The reason I ask is that this query in MySQL > returns results, yet in PostgreSQL it does not. Without showing the tables and the data in it, it's fairly hard to tell. I think MySQL does case insensitive string comparisons; check that. > I read a post about PostgreSQL not supporting outer joins, but I don't > have enough experience with SQL to determine if this is such a query > or not. Please advise. This is not an outer join. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] PL/pgsql EXECUTE 'SELECT INTO ...'
Tom Lane writes: > I am inclined to keep our options open by forbidding EXECUTE 'SELECT > INTO ...' for now. That's more than a tad annoying, because that leaves > no useful way to do a dynamically-built SELECT, but if we don't forbid > it I think we'll regret it later. You can always use CREATE TABLE AS. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Wierd postgres Problem
Najm Hashmi writes: > cmdb=# create table media_received ( > cmdb(# comp_id not null, > cmdb(# dept_id not null, ^ Those two fields should have a data type. Same in your other examples. > cmdb(# date_rec timestamp default 'now', > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "not" -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] combining
Frank Morton writes: > These two queries get me what I want from each table: > > select unique id from table1 where lastname='morton'; > select unique id from table2 where ((type = "pie") and (content = 'apple)); > > What is the best way to combine these into one query? select table1.id from table1, table2 where table1.id = table2.id and lastname = 'morton' and type='pie' and content = 'apple'; Or in 7.1, optionally: select id from table1 natural join table2 where lastname = 'morton' and type ='pie' and content = 'apple'; -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] PL/PGSQL Cook Book
mark proctor writes: > I've been trawling through the mailist lists and I notice there was > talk back in 1999 abouta PLPGSQL Cook Book - did anything come of > this? If no one is maintaining something like this and people think > its a good idea I think we should have another crack at it. I'd be > happy to maintain something like this and put it up on the web, > although I'm only a newbie and would rely upon user contribution. Start writing, and send it to [EMAIL PROTECTED] It doesn't have to be a great work of literature; incremental work is just fine. >How can I create Tree structures? >Are recursive functions supported? >Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, >PL/Perl, PL/Tcl? >How do variable scopes work in PL/PGSQL? >Can I output variables from a function to the command line for debugging purposes? >How to debug PL/PGSQL? >Various examples for each of the statements All valid questions. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] COPY isn't working right for me
Jeff S. writes: > create table member ( > member_id serial not null, > fname varchar(25) not null, > lname varchar(25) not null, > member_since date not null, > Primary Key (member_id) > ); > My member.txt file looks like this: > > Joe Smith 2000/01/14 > Frank Jones 2000/06/21 > Mike Davis 2000/09/24 > > Here's the copy command I use: > > COPY member FROM '/tmp/member.txt'; > > But I'm getting the following error: > ERROR: copy: line 1, pg_atoi: error in "Joe": can't > parse "Joe" > > I'm assuming this has to do with the member_id with > type serial. How do I import into this without having > to add the OID's to each of the rows in the text file? You can't, using COPY. You'll have to preprocess your file, either into INSERT statements, are prepend your own id's. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] cannot create sequence
Olaf Marc Zanger writes: > as it seems postgresql 7.0 has trouble to create > > ver_id_seq Define "trouble". -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Re: [ADMIN] TPCH questions
Victor Muntes Mutero writes: > select * from (select * from supplier); (for example) No subselects in FROM before 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Contributing Documentation to PG
Roberto Mello writes: > I finally came around to writing some extra documentation for PL/PgSQL > (and maybe PL/Tcl) and how to do PL/SQL-to-PL/PgSQL porting. I saw this on your site -- very nice. Can you give use the source code, so we can integrate it? > I downloaded the documentation sources, intending to use its nice > configure/make scheme but I can't find the configure script as described > in the current docs. Where can I find it? The same configure script that you use when you build the PostgreSQL software itself, the one in the top level directory. > I know I can just use the Makefiles with a little tweaking, but I am > wondering if I am missing something here. No tweaking should be required, if you have your SGML catalogs set up correctly. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] sum(bool)?
Olaf Zanger writes: > i'd like to add up the "true" values of a comparison like > > sum(a>b) sum(case when a>b then 1 else 0 end) of maybe even just select count(*) from table where a>b; -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Weird NOT IN effect with NULL values
Frank Joerdens writes: > When doing a subselect with NOT IN, as in > > SELECT name > >FROM customer > WHERE customer_id NOT IN ( > SELECT customer_id > >FROM salesorder > ); > > (from Bruce Momjian's book) > > I get no rows if the result column returned by the subselect > contains NULL values. It works as expected if I remove the NULL values > >from the result set. Is this behaviour correct and if so, why? It is correct. customer_id NOT IN (value1, value2, value3, ...) (which is what the subselect would essentially resolve to) is equivalent to NOT (customer_id = value1 OR customer_id = value2 OR customer_id = value3 ...) Say value2 is NULL. Then we have NOT (customer_id = value1 OR customer_id = NULL OR customer_id = value3 ...) NOT (customer_id = value1 OR NULL OR customer_id = value3 ...) NOT (NULL) NULL which means FALSE in a WHERE condition, so no rows are returned. Note that 'xxx = NULL' is different from 'xxx IS NULL'. Also note that NULL is not the same as FALSE in general. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] SQL copy from csv with explicit field ordering
Terry Fielder writes: > Is there somewhere that I can either enable the first line of CSV as > header names > > OR > > Can I explicitly define my import field ordering from within the select > statement? No and no. You will have to preprocess your file. Something like this will probably do: #! /bin/sh IFS=, cat "inputfile" | \ while read COL1 COL2 COL3; do echo "$COL3,$COL2,$COL1" >> "outputfile" done -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [GENERAL] Date question
Boulat Khakimov writes: > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. How about CURRENT_DATE + INTERVAL '12 months'? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] cannot get CREATE TABLE AS to work
Creager, Robert S writes: > psql -d tassiv -c "\ > create table observationsII ( \ > ra float8 not null, \ > decl float8 not null, \ > mag float8 not null, \ > smag float8 not null, \ > obs_id serial, \ > file_id int4 references files on delete cascade, \ > star_id int4 references comp_loc on delete set null default null ) \ > AS select o.ra, o.decl, o.mag, o.smag, o.file_id from > observations o" > > ERROR: parser: parse error at or near "AS" Either you have an AS clause, or a column list, not both. > And the next question, should this really be taking 3 hours to insert 315446 > records? I noticed the disk is basically idle during the few times when I > watched. Would this be because of the index created on obs_id? Quite likely. Also make sure you wrap the inserts into a BEGIN/COMMIT block. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] recompiling to use gnu readline?
Andrew Perrin writes: > I inadvertantly compiled pg 7.0.3 without gnu readline installed, so I now > don't have access to lots of the nice command-line utilities it would have > conveyed. Is there any way to add in the functionality now, or do I need > to recompile? Need to recompile. > If I need to recompile, how can I do so without wiping out > existing data? Just don't wipe it out. You can use the new executable with existing data. In fact you only need to recompile psql (src/bin/psql) after re-configuring, not the postmaster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/PgSQL and NULL
Andrew Perrin writes: > I'm trying to write what should be a simple function that returns the > minimim of two integers. The complication is that when one of the two > integers is NULL, it should return the other; and when both are NULL, it > should return NULL. Functions involving NULLs don't work well before version 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Fwd: Silly question about numbering of rows?
Ken Corey writes: > It sounds easiest to me to just punt and number the rows as they are returned > in my calling application... The rows are automatically numbered. In libpq, you'd use PQgetvalue(result, rownumber, fieldnumber). Other interfaces have similar functions. If the interface has a "fetch"-like approach, it seems trivial to keep a counter. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/pgSQL "compilation error"
Bruce Momjian writes: > There is a PL/PgSQL HowTo that we are trying to integrate into the docs > for 7.1. Anyone have a URL or status on that? It's not a PL/pgSQL Howto, it's just a documentation how to port from Oracle's PL/SQL. The status is "done". The author expressed interest in more work on the PL/pgSQL documentation; we'll see what comes of it. http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Postgres & XML
Najm Hashmi writes: > Hi all, I was just wondering if there is way or some sort of utility to > incorporate XML in postgrres. This question is about as generic as "Is there a way to incorporate ASCII in Postgres?" Depending on what you want you might find Cocoon useful. See <http://xml.apache.org/cocoon/index.html>. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Serials.
Grant writes: > (1) Why is a sequence limited to 2147483647, it seems very small? Because that's what a four-byte signed integer takes. No one has stepped forward to implement 8-byte sequence counters, yet. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey Why did you reset the sequence in the first place? You should probably set it back to where it was (using setval()). Sequences simply return incrementing numbers, they don't fill holes or have any constraint-avoiding logic. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] triggered data change violation
Tom Lane writes: > Cedar Cox <[EMAIL PROTECTED]> writes: > > Added note: The trigger is a BEFORE trigger. > > AFAIK the "triggered data change" message comes out of the AFTER trigger > code. You sure you don't have any AFTER triggers on the table? Perhaps > ones added implicitly by a foreign-key constraint? A "triggered data change violation" happens everytime you change twice within a transaction a value (column) that is part of a foreign key constraint (don't recall exactly which part). This error shouldn't really happen, but I recall there were some implementation and definition problems with deferred constraints. ...FAQ alert... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Help
Mohamed ebrahim writes: > I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. Use a cron job. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] paging
A. Van Hook writes: > #!/usr/bin/expect > > set query [lindex $argv 0] > set query [lindex $argv 1] > > spawn psql imdb -U hook -n > expect "Password: "; > send "$passwd\n "; > expect "imdb=# "; > send "$query;\r"; > expect "imdb=# "; > send "\q;\r"; > > The problem is that long query returns invoke the paging option so you > can scroll a page a time. Paging doesn't work properly within the > script and even if it did , I would have to disable it in order to > redirect the output to a file or a pipe. Try psql -P pager to turn the pager off. Also you can probably use export PGPASSWORD=xyz psql ... to skip the password probe. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Calling Java from psql (was Re: [SQL] requesting help)
Mathijs Brands writes: > Has anybody ever tried calling Java code from a pgsql trigger written > in C? Shouldn't this be possible using JNI? I have, and given the current Java implementations it's a desaster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Replace into...?
Diehl, Jeffrey writes: > I'm in the process of migrating a database and application suite from mysql > to postgresql. The problem is that I've used > mysql's "replace into..." quite frequently... begin transaction; update ... insert ... commit; or some permutation thereof. If you need to do a lot of this, writing a PL/pgSQL function might be worthwhile. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Calling Java from psql (was Re: [SQL] requesting help)
A James Lewis writes: > Before I go investigating this, is it possible to trigger an arbitrary > program from the SQL, say a shell script? At the lowest level, you can generally do anything a C program can do. Writing the equivalent of system() in SQL should be rather trivial. You can also take a look at my little toy project PL/sh: http://www.postgresql.org/~petere/plsh.html -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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: Calling Java from psql (was Re: [SQL] requesting help)
Tom Lane writes: > I'm generally pretty suspicious of any system design that requires > calling outside programs from an SQL function. The problem is that > this fundamentally breaks transactional semantics: if the transaction > is rolled back after the function call, its effects inside the database > disappear ... but there's no way to roll back whatever the outside > program did. Now you have a consistency problem. The trick here is to organize your outside programs into fairly atomic chunks and do conservative error logging. The need to synchronize the non-database world with the database is definitely real. But usually a regular function call near the end of the transaction block is much more appropriate than a trigger function. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'Include' function in SQL scripts
Alessio Bragadini writes: > I was wondering if PostgreSQL supports some kind of #include between SQL > script files, to split a long script in different files. If you're using the psql client then \i filename can be used. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Max Size of a text field
Koen Antonissen writes: > I just recieved this error: > 'Warning: PostgreSQL query failed: ERROR: Tuple is too big: size 13872, > max size 8140 ' > > Is there anyting I can do about that other than tell my users just not > typing so much text into the field? Option 1: increase BLCKSZ in src/include/config.h Option 1.5: use lztext (7.0* only) Option 2: upgrade to 7.1 -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] primary key scans in sequence
bernd writes: > hey i have the following table def (834.000 rows, vaccum analyze'd): > dl_online=# \d mitglied > Table "mitglied" >Attribute| Type | Modifier > +--+ > mitgliedid | bigint | not null > dlnummer | varchar(30) | > vorname| varchar(50) | > zuname | varchar(50) | not null > geburtsdatum | varchar(20) | > aktiv | boolean | not null default 't'::bool > strasse| varchar(255) | > plz| varchar(25) | > ort| varchar(255) | > telefon| varchar(255) | > eintrittsdatum | varchar(20) | > geschlechtid | bigint | not null default 3 > treuelevelid | bigint | not null default 1 > clubmitglied | boolean | not null default 'f'::bool > bemerkungen| text | > mid| bigint | > > Indices: mitglied_dlnummer_idx, [on dlnummer] > mitglied_pkey [on mitgliedid] > > 2) get one row over the primatry key (mitgliedid): > dl_online=# explain select * from mitglied where mitgliedid = 833228; > NOTICE: QUERY PLAN: > Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154) > EXPLAIN This is because the system is not smart enough to match up a bigint = integer comparison with an index scan on a bigint column. Quoting the number, '833228', should fool it sufficiently to make this work. Incidentally, it seems unlikely that you need to use bigint for membership ids, unless you plan on more than 2*10^9 members. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] please help
Loïc Bourgeois writes: > What is the equivalent of the oracle request: SELECT ... FOR UPDATE > NOWAIT, under PostGreSQL I don't know Oracle, but there doesn't seem to be such a command in PostgreSQL. If the table is already locked, the SELECT FOR UPDATE has to wait. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] how to do this join ?
[EMAIL PROTECTED] writes: > Hello there > > I have another SQL question. Please see the example : > > select *, > (select a_typ from auftrag where a_nr=z_a_nr) as typ, > (select a_t_definition_d from auftrags_typ where a_t_code=typ) as text > from zylinder select zylinder.*, auftrag.a_typ (select a_t_definition_d from auftrags_typ where a_t_code = auftrag.a_typ) from zylinder, auftrag where auftrag.a_nr = zylinder.z_a_nr; or, using 7.1, maybe something like select zylinder.*, auftrag.a_typ, auftrags_typ.a_t_definition from (zylinder join auftrag on a_nr = z_a_nr) left join auftrags_typ on a_t_code = a_typ Other variations are possible, depending on the referential contraints you have between the tables. > > I have three tables that I need data from. I'd like to use the to > temporary store the kind of auftrag and then use it to get the > definition (clear text) from another table. > > The query returns that typ is not known . -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Casting numeric to text
Hans-Jürgen Schönig writes: > Is there any possibility to cast numeric to text in Postgres 7.0.3? > > shop=# select cast(price as text) from products; > ERROR: Cannot cast type 'numeric' to 'text' Use the to_char() function. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] open and closed paths ...
Hans-Jürgen Schönig writes: > I am using PostgreSQL 7.0.3. I have tried the isopen() function but it > doesn't seem to work (or it is not fully implemented yet). > In my opinion the third record is a closed path but the isopen() > functions return f. For no good reason apart from ancient tradition, paths enclosed in parentheses, like ((3,1),(2,8),(10,4)), are implicitly closed. To make an open path brackets should be used, like [(3,1),(2,8),(10,4)]. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Casting numeric to text
Cedar Cox writes: > When would one want to use cast()? What is the difference between cast > and :: ? After a quick look in the documentation I couldn't find > anything.. cast() is SQL, :: is traditional Postgres. :: may go away in the distant future to make room for the SQL feature that is supposed to use ::. (It escapes me at the moment what that was.) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] please help
Cedar Cox writes: > It would be somewhat (very) useful to have something like this. We were > toying with the idea of making some sort of system to figure out if a > table is locked or not. This will probably introduce race conditions unless done very carefully. In theory you need a second level of locks to protect the information you obtained regarding the "real" locks. I'm not saying it's impossible, but 20 years ago people were writing Ph.D. theses about these sort of things. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: open and closed paths ...
Hans-Jürgen Schönig writes:
> The only problem left is the correct syntax of the command when using [].
> I've tried some versions but it did not work.
> Maybe Tom can include an example into the docs.
>
> Hans
>
>
> shop=# INSERT INTO temppath(fieldname) VALUES '((1,3), (4,12))';
> ERROR: parser: parse error at or near "'"
Should be VALUES ('((1,3), (4,12))'); The outer parentheses belong to the
INSERT command, the quotes delimit the data literal, whatever is inside
the quotes is the data type's business.
> shop=# INSERT INTO temppath(fieldname) VALUES ((1,3), (4,12));
> ERROR: parser: parse error at or near ","
> shop=# INSERT INTO temppath(fieldname) VALUES ('(1,3), (4,12)');
> INSERT 51947 1
> shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)'];
> ERROR: parser: parse error at or near "["
> shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]';
> ERROR: parser: parse error at or near "'"
> shop=# INSERT INTO temppath(fieldname) VALUES '[(1,3), (4,12)]'::path;
> ERROR: parser: parse error at or near "'"
> shop=# INSERT INTO temppath(fieldname) VALUES ['(1,3), (4,12)']::path;
> ERROR: parser: parse error at or near "["
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
---(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 numeric to text
Ross J. Reedstrom writes: > FYI, I can't find an occurance of '::' that's not part of '::=' in either > SQL1992.txt or the ansi-iso-[sql]-1999.txt files I've got. SQL 1999 6.12 ::= [ ] That syntax even makes sense... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: select substr???
Tim Johnson writes: > Hi, > > I have postgres 6.x (where x is something). > > I have the following list of data > > data > > ABC* > ABC > ABC- > ABC+ > ABC > QWE~ > ASD+ > ASD# > KLASDHK- > KLASDHK+ > KLASDHK > KLASDHK* > > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > characters] order by data' Write a function that strips off the suffixes and do 'select distinct voodoo(data) ...'. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Making SELECT COUNT(seed) FROM fast
Gerald Gutierrez writes: > I have a table with about 5 million rows in it. I need to be able to get the > exact number of rows in the table at runtime. So I tried the following: > > xxx=> explain select count(seed) from mytable; > NOTICE: QUERY PLAN: > > Aggregate (cost=103152.27..103152.27 rows=1 width=4) > -> Seq Scan on mytable(cost=0.00..89756.42 rows=5358342 width=4) > Actually executing this query takes about 2 minutes on a P3-800MHz machine > with 512MB of RAM. > > I have an index on the seed table, and I have done VACUUM ANALYZE on the > table after inserting the rows. Is there any way I can get this to be fast? For a count of all rows you necessarily need to visit all rows (at least in this implementation), so an index is of no use. For a sequential scan with little computation involved this really comes down to pure hardware speed. You might be able to speed it up a little by using count(*) instead. Note that there's a semantic difference, because count(seed) doesn't count the rows where seed is null, which is probably not what you intended anyway. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] DROP TABLE in transaction
David Olbersen writes: > I was wondering if anybody could explain to me why I can't roll back dropping a > table. Because DROP TABLE removes the table file on disk, and you can't roll back that. Actually, in 7.1 you can. ;-) > I would think that of all the events that should be rollback-able, > dropping a table would be the first on the list. Naah. Insert and update are first. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] enumerating rows
Kovacs Zoltan writes: > Maybe a trivial question, maybe it's foreign from SQL, I'dont know... > How to add a column which stands for the row number in each row of the > result? E.g.: > > row_no | column1 | column2 | ... > ---+-+-+ ... > 1 | datum11 | datum12 | ... > 2 | datum21 | datum22 | ... >... | ... | ... | ... > > I didn't find anything in the docs. Your client can do that. When you fetch the results you have to have some sort of loop anyway, so you have (or keep) a counter there. There's no use of the server generating this information, because the numbering is implied by the order in which the rows are sent. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] BOOLEAN data type?
Josh Berkus writes: > That the following query: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required; > > ... should be equivalent to: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required = TRUE; > > However, in testing (7.1 RC2), the query turned out to mean this: > > SELECT detail_id FROM order_detials > WHERE order_usq = 7703 AND detail_required IS NOT NULL; No way. You're doing something wrong. How about showing the data that makes you believe this? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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] Using Random Sequence as Key
Bernardo de Barros Franco writes:
> I wanted to index a table by a random key. Exemplifying, when a insert is
> made, the id value is automatically filled with a random number between
> 1 and 9.
=> create table test (id int default random() * 8 + 1, content text);
CREATE
=> insert into test (content) values ('hi');
INSERT 36163 1
=> insert into test (content) values ('there');
INSERT 36164 1
=> insert into test (content) values ('blah');
INSERT 36165 1
=> select * from test;
id | content
---+-
61616 | hi
72605 | there
83469 | blah
(3 rows)
Verifying the default expression for correct boundaries is left as an
exercise. ;-)
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Using Random Sequence as Key
Bernardo de Barros Franco writes:
> But my only question would be, in the example quoted would id be really the
> table index and is it unique?
Making unique *and* random numbers is a pretty complex (and slow) thing to
do in general. Consider redesigning your application.
>
> Thank you
>
> Quoting:
> >Bernardo de Barros Franco writes:
> >
> > > I wanted to index a table by a random key. Exemplifying, when a insert
> >is
> > > made, the id value is automatically filled with a random number between
> > > 1 and 9.
> >
> >=> create table test (id int default random() * 8 + 1, content
> >text);
> >CREATE
> >=> insert into test (content) values ('hi');
> >INSERT 36163 1
> >=> insert into test (content) values ('there');
> >INSERT 36164 1
> >=> insert into test (content) values ('blah');
> >INSERT 36165 1
> >=> select * from test;
> > id | content
> >---+-
> > 61616 | hi
> > 72605 | there
> > 83469 | blah
> >(3 rows)
> _
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] function to format floats as money?
Mark Stosberg writes: > I'm curious to know if there is a function available in Postgres 7.0.3 > (or 7.1) that will format a float style number as "money". I understand > that the money type is going away in the future, so using a float type > that is sometimes formatted like money seems like a good alternative. So > ideally, I'm looking for a solution that won't go away when the money type > does. :) Thanks! to_char() for formatting. numeric for storage. Using floats for monetary amounts is not only an extremely bad idea because of the inexactness of storage and arithmetic, it might even be illegal if you're using it for official purposes. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] is this proper sql?
clayton cottingham writes: > hi one of the developers here at work say this should work > insert into detail (det_id,det_mas_id,det_date,det_amt) values > (0,0,now(),'0'), (1,1,now(),'1'); This is real SQL (except for the now() function) but it isn't supported in PostgreSQL yet. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RI permission problem
Kyle writes: > Is there a way to get this to work without granting update to table b? Update to 7.1. > Tom, I understand someone was working on setuid functions. Is that a > long way off? It would be nifty if triggers could execute with the > privileges of the user that created them rather than the user who is > executing them. This would help greatly in closing some security holes > like this we are dealing with. It wouldn't be hard to implement, but there were some disputes about the particular method of how to do it. Maybe 7.2. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] RI permission problem
Kyle writes: > Peter Eisentraut wrote: > > > Kyle writes: > > > > > Is there a way to get this to work without granting update to table b? > > > > Update to 7.1.] > > I'm on 7.1. Should an RI trigger under 7.1 run as the DBA or as the current > user? Okay, we missed a few cases. Try the attached patch. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter Patch ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SET SEQSCAN TO OFF - error
Hans-Jürgen Schönig writes: > I have the command below like it is described in the documentation > (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. > > SET SEQSCAN TO OFF; > ERROR: 'seqscan' is not a valid option name > > Does anybody know why? Because 'seqscan' is not a valid option name. Maybe you mean enable_seqscan. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] simulate union in subselect
Wei Weng writes: > I know this is not do-able, but is there any way to simulate the > following in Postgresql 7.1? > > select id from > (select id, recv_date as date from table1 > union > select id, send_date as date from table2) AS subtable > order by date; I haven't actually tried whether this does not work, but ISTM that you could simply do select id, recv_date as date from table1 union select id, send_date as date from table2 order by 2; and ignore the second column when processing the result. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Using Transaction Blocks w/ SELECT
[EMAIL PROTECTED] writes: > Goinging throught the libpq docs, I noticed that in all of the examples involving > select statements, transaction blocks are used. I see why this is necessary for > write operations, but I don't see the need in read operations that don't commit. Am > I missing something? Any help appreciated. It's done to get consistent data snapshots across several commands. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] '13 months ago'::reltime
Christopher Sawtell writes: > On Friday 04 May 2001 06:32, you wrote: > > Type reltime is old and deprecated. Don't use it. > > What should we be using instead? interval -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] RI permission problem
Kyle writes: > Shouldn't the select access to the view trickle down to subordinate select functions? I would think not. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is function atomic?
John Hasler writes: > > Do you have any idea when [nested transactions] will [be added]? > > Richard Huxton writes: > > Check the "todo" list in the developers' area on the website - that'll > > show what's planned for 7.2 > > It's listed there: that's why I asked. Is everything on that list planned > for 7.2? No. The TODO list is just a list of random ideas, some better than others. Most seasoned developers have their own private lists of things that they would like to get done. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] PL/TclU
Roberto Mello writes:
> I've heard quite a bit about PL/TclU, but what's the difference
> between that and regular PL/Tcl??
It's untrusted ("U") and can do anything a normal Tcl interpreter can do.
Regular PL/Tcl is constrained in various way to make it safe to offer it
to normal users.
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(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] Is function atomic?
Wei Weng writes: > If it is not, is it possible to acquire a lock on a row ? how about a > lock on a table? SQL statements issued by server-side functions operate with the same transaction isolation semantics as "normal" SQL statements. However, whatever the function does outside the database may obviously have different semantics. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Is function atomic?
Wei Weng writes: > Does that mean if I used > DECLARE > ... > BEGIN > DO_STUFF > END; > > the DO_STUFF will not be interrupted (maintain atomicity) even when > multiple threads use the function concurrently? Interruption, atomicity, and concurrency are separate issues. The function could of course be interrupted if there's an error. (That error may be related to concurrency, such as a serialization failure.) The database interactions of the function will be atomic in the sense that rollback will work. Concurrent execution of a function is permitted, but there may be issues if you modify global state or there is a serialization failure. These are the same issues that you have to deal with in any programming environment. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Is function atomic?
Wei Weng writes: > What I wanted is simple: > I don't want other processes that run the function at the same time have > read/write access from the tables this function accesses(select and > insert/update). Is it possible, say, let other proccess blocked on this > function? See LOCK command. But note that this would lock out process in this function or any other access to these tables. Maybe you should take a look at the userlock module in contrib. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] While Using COPY COMMAND ...
Bhuvan A writes: > bhuvan=# copy f_addr_phones with oids to > '/home/bhuvan/fap-table'; > ERROR: COPY command, running in backend with effective uid > 1015, could not open file '/home/bhuvan/fap-table' for > writing. Errno = Permission denied (13). The server is probably running under a different user id (postgres?). -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] finding current oid
postgresql writes: > Is there a way to return the current oid of a transaction? Transactions don't have oids, only table rows do. The libpq function PQoidValue() will possibly give you the last affected oid. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CREATE TYPE function examples
Roberto Mello writes: > I'm looking for some examples of how the functions for CREATE TYPE > should be. There are none in the documentation, so I was hoping to find > someone here that could help me. http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/xtypes.html This seems to be outdated regarding the fmgr update, though. (Hint, hint...) -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] ERROR: Procedures cannot take more than 16 arguments
Kristis Makris writes: > ERROR: Procedures cannot take more than 16 arguments > Can this limit be increased by manipulating some variable in the > postgres source code? If so, how would I go about finding which file + > variable I need to alter? FUNC_MAX_ARGS in config.h; see the comments there. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] can we write to a flat file from Postgresql procedure
R Vijayanath writes: > It would be great if you can tell me if I can write a > procedure that can write the output to the OS(Linux > OS) file. You could try out PL/sh for that. http://www.postgresql.org/~petere/plsh.html YMMV -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] How can we match a condition among 2 diff. tables?
[EMAIL PROTECTED] writes: > Now, i need to get the details of all employees who did > receive NONE of the salesorders. ie.. i wish to select the > records of table 'employee' whose 'emp_id' are not > there in table 'salesorder'. SELECT * FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM salesorder); or, slightly uglier but possibly faster SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM salesorder WHERE salesorder.emp_id = employee.emp_id); -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Hey! ORDER BY in VIEWS?
Josh Berkus writes: > Hey! I thought you couldn't do ORDER BY in views ... yet I just did. > Is this a new thing, or am I just getting my Trasact-SQL and my > PostgreSQL mixed up again? I think it was allowed from 7.1 on to enable LIMIT in views to work sensibly. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Cross database foreign keys
Morgan Curley writes: > Does anyone know if it is possible to connect to a differernt db from > within a plsql function. > I have multilple inter-related schemas and want to enforce some fk > relationships. Not possible -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] interpreting attributes in pg_class
Markus Wagner writes:
> I need to find the entries for "my" tables within pg_class, without the
> indexes ("..._pkey").
> How does one have to interpret "reltype" and that, and where are the
> docs/header files?
http://www.de.postgresql.org/users-lounge/docs/7.1/postgres/catalog-pg-class.html
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] First steps in plpgsql - language not recognized?
man createlang -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
Chris Ruprecht writes: > I need to know how I can access a flat file from within a PL/PGSQL script. You can't. PL/TclU could help you there. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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
