[GENERAL] Dilbert Experiences a 'Database Manager' moment
Priceless cartoon in middle of page: http://cbbrowne.com/info/rdbms.html -- "You are behaving like a man", is an insult from some women, a compliment from an good woman. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] convert string function and built-in conversions
It seems to me that these values should be the same:
select 'lydia eugenia treviño', convert('lydia eugenia treviño' using
ascii_to_utf_8);
but they seem to be different. What am I missing?
culley
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] plpgsql
We are trying to make some things work with plpgsql. The problem is that I built several functions that call one another, and I thought that the way of calling it was just making the assign: var:=func1(arg1,arg2); which gave me an error near ")". Now if I did the same, but like this: PERFORM ''SELECT func1(arg1,arg2)''; it didn't give the error anymore. The problem was that the other function (func1()) aparently didn't get executed (logs stop at the PERFORM). Am I doing something wrong? -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(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: [GENERAL] Timestamp to date conversion...plz help me
> I want to extract date part (mm/dd/yy or any other date format) of Time= > stamp in postgreSQL. Can anyone help me out how I can proceed?. You have at least two choices: select current_timestamp::date; will give you the date in the default date format. select to_char(current_timestamp,'mm/dd/yy'); gives you control over the formatting. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] 7.4b4 domain usage and select question
On Sat, Oct 18, 2003 at 01:40:04PM -0600, Robert Creager wrote: > But, I cannot turn of logging of the duration! It's set to > log_min_duration_statement = 0, but still logs the duration of every > statement. This didn't happen with 7.4b4... I've set it high to not > log most statements. The value has a different meaning now. You have to set it to -1 to disable logging IIRC. -- Alvaro Herrera () "In a specialized industrial society, it would be a disaster to have kids running around loose." (Paul Graham) ---(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: [GENERAL] 7.4b4 domain usage and select question
When grilled further on (Sat, 18 Oct 2003 15:46:55 -0400), Tom Lane <[EMAIL PROTECTED]> confessed: > Robert Creager <[EMAIL PROTECTED]> writes: > > But, I cannot turn of logging of the duration! It's set to > > log_min_duration_statement = 0, > > -1 turns it off now. > Thanks. Didn't even thing of doing another initdb to see if the setting may of changed... Cheers, Rob -- 13:54:49 up 78 days, 6:20, 4 users, load average: 2.00, 2.00, 2.00 pgp0.pgp Description: PGP signature
Re: [GENERAL] 7.4b4 domain usage and select question
Robert Creager <[EMAIL PROTECTED]> writes: > But, I cannot turn of logging of the duration! It's set to > log_min_duration_statement = 0, -1 turns it off now. regards, tom lane ---(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: [GENERAL] 7.4b4 domain usage and select question
When grilled further on (Sat, 18 Oct 2003 13:12:41 -0400), Tom Lane <[EMAIL PROTECTED]> confessed: > Robert Creager <[EMAIL PROTECTED]> writes: > > select * from test_table where field_1 = '1'; -- fails > > The last select fails with 'operator is not unique: test_domain = "unknown"' > > Works as expected in CVS tip ... > > regards, tom lane Per your suggestion, I grabbed the nightly snapshot. It works fine for the domain problem I was encountering. But, I cannot turn of logging of the duration! It's set to log_min_duration_statement = 0, but still logs the duration of every statement. This didn't happen with 7.4b4... I've set it high to not log most statements. Cheers, Rob -- 13:36:33 up 78 days, 6:02, 4 users, load average: 2.00, 2.00, 2.00 pgp0.pgp Description: PGP signature
Re: [GENERAL] Timestamp to date conversion...plz help me
On Sat, Oct 18, 2003 at 10:21:03PM +0530, Jitender Kumar C wrote: > Hi, > I want to extract date part (mm/dd/yy or any other date format) of > Timestamp in postgreSQL. Can anyone help me out how I can > proceed?. SELECT your-timestamp-value::date or, more standard, SELECT CAST(your-timestamp-value AS date) -- Alvaro Herrera () "When the proper man does nothing (wu-wei), his thought is felt ten thousand miles." (Lao Tse) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] restart and postgres.conf
> El Vie 17 Oct 2003 16:43, escribió:
> >> Should "restart" with pg_ctl or
> /etc/init.d/postgres
> >> cause postgres.conf to be reread? It doesn't
> appear
> to
> >> do so for me (another oddity - after "restart"
> the
> >> last line in the log is "database system is shut
> >> down"). "stop" followed by "start" works fine.
>
> > Maybe you are editing the wrong postgresql.conf
> file.
There's only one postgresql.conf on my system.
> >> Feature request - add "reload" to
> /etc/init.d/postgres
>
> ># /etc/init.d/postgresql
> >Uso: /etc/init.d/postgresql
>
>{start|stop|status|restart|condrestart|reload|force-reload}
>
> >You should tell us what OS you are using.
Redhat 9. I just looked in contrib/startup-scripts
for PG 7.3.4 and both scripts there have just
start/stop/restart/status.
CSN
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] 7.4b4 domain usage and select question
I'm using 7.4b4 with domains, and am having a problem with selecting without casting. create domain test_domain as integer check( (value notnull) and (value >= 1) ); create table test_table( field_1 test_domain, field_2 integer ); insert into test_table values( 1, 1 ); insert into test_table values( '2', '2' ); select * from test_table where field_2 = '1'; select * from test_table where field_1 = '1'; -- fails The last select fails with 'operator is not unique: test_domain = "unknown"' The insert with quoted values works fine without casting. The HINT indicates I need to cast, which works find with either '1'::integer or '1'::test_domain. Is this the correct behavior? Do I have to cast every quoted value? The values are quoted through the usage of DBD::Pg. Thanks, Rob -- 10:32:54 up 78 days, 2:58, 4 users, load average: 2.00, 2.00, 2.00 pgp0.pgp Description: PGP signature
Re: [GENERAL] 7.4b4 domain usage and select question
Robert Creager <[EMAIL PROTECTED]> writes: > select * from test_table where field_1 = '1'; -- fails > The last select fails with 'operator is not unique: test_domain = "unknown"' Works as expected in CVS tip ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpgsql: return multiple result sets
--- Oksana Yasynska <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm running Postgres 7.2.1 and I need to return > multiple row sets from plpgsql > function. I'm new in plpgsql but according > documentation and everything I > could find in the mailing list I need to switch to > 7.3 to get at least SETOF > rows as a result. > > I can't really upgrade Postgres now. Is there is any > a workaround idea to > retrieve multiple rowsets? > > I have up to 50 tables in database to join and pass > this data to the another > application > I had an idea to build a view and retrieve cursor on > this view (if I stay with > 7.2) or generate custom type based on the columns of > all 50 tables and > retrieve a SETOF custom type (if I use 7.3) > > Can anybody give me any suggestion? You can return a cursor from your function, which you can then use in your application. Sort of like: create function my_cursor_test(refcursor, integer) returns refcursor as 'begin open $1 as cursor for select * from mytable where id = $2; return $1; end;' language 'plpgsql'; Then call it like: begin; select my_cursor_test(mycursor, 1); select * from mycursor; (output comes here) end; Note the need to wrap the statements in an explicit transaction. With statements being autocommitted, the cursor would be closed immediately following the function call. Better check the syntax too, I just dashed that off (hey, it's Saturday). It's all there in the 7.2 docs under "procedural languages". __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Timestamp to date conversion...plz help me
Hi, I want to extract date part (mm/dd/yy or any other date format) of Timestamp in postgreSQL. Can anyone help me out how I can proceed?. Regards, Ch.V.J. Kumar |Associate Consultant | iGATE Global Solutions Limited | Office: 5521701 xtn 3031 | 9886219429| [EMAIL PROTECTED]
Re: [GENERAL] Postgres seems to BE on windowsXP
Postgres will run on windows under cygwin, you can also set it up as a service. Native win32 support will be coming to PostgreSQL sometime soon (not 7.4 but soon). If you wanted Postgres on windows now you could get mammoth postgresql from the folks at commandprompt.com. Jason Dennis Gearon wrote: For a price, you can get WAP2 Windblows Apache Postgres PHP http://www.phpgeek.com/ "*2003-08-29 - PostgreSQL Running in Pragmapool Labs* Woohoo! I've finally gotten a version of PostgreSQL running on Windows XP. It's running, PHP can access it, phpPgAdmin works for administration and my test databases work. This means that a PostgreSQL module is now in the queue for development (a few other things need to get finished first)." ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] plpgsql: return multiple result sets
Hi all, I'm running Postgres 7.2.1 and I need to return multiple row sets from plpgsql function. I'm new in plpgsql but according documentation and everything I could find in the mailing list I need to switch to 7.3 to get at least SETOF rows as a result. I can't really upgrade Postgres now. Is there is any a workaround idea to retrieve multiple rowsets? I have up to 50 tables in database to join and pass this data to the another application I had an idea to build a view and retrieve cursor on this view (if I stay with 7.2) or generate custom type based on the columns of all 50 tables and retrieve a SETOF custom type (if I use 7.3) Can anybody give me any suggestion? Thank you in advance, Oksana ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to quote text before inserting into database?
Hi Scott,
Scott Chapman wrote:
I am working with Python (psycopg). I have HTML with embedded Python that I'm
inserting into a database and it could contain any character.
Single quotes, at least, must be escaped (to two single quotes, right?) before
inserting it into Postgres.
This poses a problem when I get the data out of the table. It could have
originally contained two single quotes together and I replace them with one
single quote in the unescaping process.
How do you properly escape the special characters (and what all are they)?
This is supported by psycopg. See
http://www.python.org/peps/pep-0249.html
Especially:
.execute(operation[,parameters])
Prepare and execute a database operation (query or
command). Parameters may be provided as sequence or
mapping and will be bound to variables in the operation.
Variables are specified in a database-specific notation
(see the module's paramstyle attribute for details). [5]
A reference to the operation will be retained by the
cursor. If the same operation object is passed in again,
then the cursor can optimize its behavior. This is most
effective for algorithms where the same operation is used,
but different parameters are bound to it (many times).
For maximum efficiency when reusing an operation, it is
best to use the setinputsizes() method to specify the
parameter types and sizes ahead of time. It is legal for
a parameter to not match the predefined information; the
implementation should compensate, possibly with a loss of
efficiency.
The parameters may also be specified as list of tuples to
e.g. insert multiple rows in a single operation, but this
kind of usage is depreciated: executemany() should be used
instead.
Return values are not defined.
This means, if you have to handle strings, you can use
cursor.execute("SELECT value FROM table WHERE key=%s",("your'key",))
for example.
HTH
Tino Wildenhain
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
