Mike Mascari <[EMAIL PROTECTED]> writes:
> "Simple SQL functions can now be inlined by including their SQL in the
> main query. This improves performance by eliminating per-call overhead.
> That means simple SQL functions now behave like macros."
> has me a bit worried. What does "simple" mean?
Stuart Bishop wrote:
> Indeed - I was under the impression that the timezone would be preserved
> (which is the case in the external datetime libraries I use), but I now
> see that PostgreSQL will lose this information.
Err - how come, lose?
Jaromir
--
Jaromir Dolecek <[EMAIL PROTECTED]>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tom Lane wrote:
| Stuart Bishop <[EMAIL PROTECTED]> writes:
|
|>How much overhead is there in storing a timestamp with timezone as
|>opposed to one without?
|
|
| Exactly zero. You have a misconception about what the datatype really
| does --- see othe
Michael Fuhr wrote:
On Thu, Oct 14, 2004 at 07:42:22PM +0200, Andreas wrote:
or is there a complement to pg_get_userbyid() ?
If there is then I've overlooked it in the documentation. It's
easy enough to write:
Thanks :)
I though it was consequent to expect such a function, since there
This is a test -- please ignore
---(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
Hi,
Can anyone tell me how to unsubscribe my address from the mailing list.
Thanks.
Regards
Joseph
On Thu, Oct 14, 2004 at 07:42:22PM +0200, Andreas wrote:
>
> I'd like to store who changed records on some tables.
> I'd prefer not to store the username but rather his/her ID.
> Will I allways have to run
> select usesysid from pg_user where usename=session_user;
> or is there a complement to pg_g
Hello.
I'm writing SQL functions that take an action code and determine the
rows visible by accessing application-maintained privilege tables.
Here's an example:
CREATE FUNCTION sql_areas(bigint) RETURNS SETOF bigint AS '
SELECT _areas.area
FROM _members, _webgroups, _stores, _areas
WHERE
Title: Complex Update Queries with Fromlist
In Microsoft SQL Server, I can write an UPDATE query as follows:
update orders set RequiredDate =
(case when c.City IN ('Seattle','Portland') then o.OrderDate + 2 else o.OrderDate + 1 end)
from orders o
join customers c on
o.Customerid = c.C
My very simple table creation test (based on their "first.py"
example is failing ... Here's what I'm trying. Non-pythonated
syntax works in pgsql:
no go:
curs.execute("""CREATE TABLE key_test (
key_col CHAR(9) PRIMARY KEY,
nother_col CHAR(256))""")
Works fine for me:
d=psycopg.
SELECT COUNT(*) from mytable
---
Bastianello Luciano
Software Consultant - Apprentice Sorcerer
e-mail: [EMAIL PROTECTED]
e-mail: [EMAIL PROTECTED]
ICQ: 209754422 - MSN: [EMAIL PROTECTED]
---
---
On Thu, Oct 14, 2004 at 02:00:51PM -0700, Michael Holden wrote:
> There is a file located at
>
> /usr/local/pgsql/data/base/26920/
>
> named 35987 that is roughly 750mb. Is there any way to find out what
> table or database this file belongs to?
Sure, use contrib/oid2name.
> I really shouldn
How about ...
select count(distinct this) from table
John Sidney-Woollett
Dev wrote:
Hello all,
I am trying to get a total number of rows returned form a query.
SELECT count(this) from table group by this
Currently it is returning x rows with a count of each of the group by.
I need the count of th
* Dev <[EMAIL PROTECTED]> [2004-10-14 16:54:56 -0400]:
> I am trying to get a total number of rows returned form a query.
>
> SELECT count(this) from table group by this
Remove the 'group by' clause -- you don't need it.
--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.com
Dev wrote:
Hello all,
I am trying to get a total number of rows returned form a query.
SELECT count(this) from table group by this
Currently it is returning x rows with a count of each of the group by.
I need the count of the rows returned!
SELECT COUNT(DISTINCT this) FROM table
Andre
http://www.postgresql.org/docs/7.4/static/datatype-boolean.html
CASE WHEN its THEN 'true' ELSE 'false' END
-tfo
On Oct 14, 2004, at 3:43 PM, Bambero wrote:
Postgres returns me 't' or 'f' from boolean field
How to change that it returns me 'true' or 'false'
replace(its, 'f', 'false') AS its
doesn't
Hi, folks
There is a file located at
/usr/local/pgsql/data/base/26920/
named 35987 that is roughly 750mb. Is there any way to find out what
table or database this file belongs to? I really shouldn't have that
much data in any of my tables, so I'm fairly certain that this is a bug
in my code
Hello all,
I am trying to get a total number of rows returned form a
query.
SELECT count(this) from table group by this
Currently it is returning x rows with a count of each of the
group by.
I need the count of the rows returned!
Any way to do this?
Brian C. Doyle
Director, In
Postgres returns me 't' or 'f' from boolean field
How to change that it returns me 'true' or 'false'
replace(its, 'f', 'false') AS its
doesn't work
Bambero
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if
Josh Close wrote:
> Is there a way to do "dirty" reads on postgres?
>
> If there is an insert of a million records or so, is there a way to
> select from those records before it's committed?
By your transaction, sure, by another, no.
--
Bruce Momjian| http://candle.p
Is there a way to do "dirty" reads on postgres?
If there is an insert of a million records or so, is there a way to
select from those records before it's committed?
-Josh
---(end of broadcast)---
TIP 8: explain analyze is your friend
Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> One's marked VOLATILE, the other is marked IMMUTABLE. This affects
> whether it's considered a constant, the planner estimates and hence
> whether it uses the index.
Is there even a way to solve it this way via a procedure?
I've tried different
David Rysdam wrote:
The README from 8.0-beta3 says "This distribution also contains
several language bindings, including C and Tcl" but I'm not finding
libpgtcl being built, nor can I find a way to tell it to. I see pgtcl
is on http://gborg.postgresql.org, so this mean that the README is out
o
Here's what I do... It's not pretty but it works.
create table auditlog (
auditwhen timestamp not null default CURRENT_TIMESTAMP,
auditwhat char(10) not null,
audittable varchar not null,
auditkeyval int not null,
auditfield varchar not null,
oldval
Hello
Does PostgreSQL provide anything comparable with the functionality of
MySQL Cluster?
I appreciate all information.
Thank you.
Tim
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe
> "GS" == Greg Stark <[EMAIL PROTECTED]> writes:
GS> David Garamond <[EMAIL PROTECTED]> writes:
GS> Another reason to move to 7.4.5 would be that each version
GS> introduced changes in behaviour. You're going to be dealing with
GS> minor headaches from things like '' not being a valid integer
The README from 8.0-beta3 says "This distribution also contains several
language bindings, including C and Tcl" but I'm not finding libpgtcl
being built, nor can I find a way to tell it to. I see pgtcl is on
http://gborg.postgresql.org, so this mean that the README is out of date
and I need to
Heck! So much for feeling close. It is somewhat frustrating to me that
such an obviously useful tool (having and using audit tables) should be
so difficult to implement. I thought I had a reasonable chance of doing
it in plpgsql because I've written functions in that before--I have no
idea how t
Scott Cain wrote:
I feel like I am getting very close, but I am still not quite there. I
rewrote the trigger function below to use execute, but now I get the
following error:
ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement
It s
Hi,
I'd like to store who changed records on some tables.
I'd prefer not to store the username but rather his/her ID.
Will I allways have to run
select usesysid from pg_user where usename=session_user;
or is there a complement to pg_get_userbyid() ?
Can I have this as a default-value for a create
On Tue, Oct 12, 2004 at 12:20:46PM -0400, Eric D Nielsen wrote:
>
> I was hoping for a get/easy solution, but those never pan out :)
Here's a trivial Python program that handles NOTIFY events; it
couldn't get much easier:
#!/usr/local/bin/python
import psycopg
import select
conn = psycopg.conn
* Steven Klassen <[EMAIL PROTECTED]> [2004-10-14 10:07:39 -0700]:
> CREATE FUNCTION check_passwd(text,text) RETURNS boolean AS 'SELECT
> CASE WHEN passwd = md5($2) THEN true ELSE false END FROM pg_shadow
> WHERE usename = $1;' LANGUAGE sql;
Strike that - go with what Michael recommended. I glance
* Thomas Hallgren <[EMAIL PROTECTED]> [2004-10-14 17:27:20 +0200]:
> I'm connected to a database and I want to verify that a username and
> password for some user is correct. I know I can verify a users existence
> by doing:
>
> select exists(select * from pg_user where usename = $1)
You can g
On Thu, Oct 14, 2004 at 05:27:20PM +0200, Thomas Hallgren wrote:
> I'm connected to a database and I want to verify that a username and
> password for some user is correct. I know I can verify a users existence
> by doing:
>
> select exists(select * from pg_user where usename = $1)
>
> but I wo
One's marked VOLATILE, the other is marked IMMUTABLE. This affects
whether it's considered a constant, the planner estimates and hence
whether it uses the index.
On Thu, Oct 14, 2004 at 05:30:58PM +0200, Robin Ericsson wrote:
> After some discussion on performance list, I guess this is back to a
>
I feel like I am getting very close, but I am still not quite there. I
rewrote the trigger function below to use execute, but now I get the
following error:
ERROR: OLD used in query that is not in a rule
CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement
It seems that I am n
On Wed, Oct 13, 2004 at 03:37:14PM -0400, David Rysdam wrote:
> Michael Fuhr wrote:
> >
> >I'd probably choose to extend PostgreSQL rather than hack what
> >already exists, though.
>
> By "extend PostgreSQL" do you mean create a custom input_function for
> timestamp? Are there docs that give hint
I'm connected to a database and I want to verify that a username and
password for some user is correct. I know I can verify a users existence
by doing:
select exists(select * from pg_user where usename = $1)
but I would like to verify the correctness of the password as well. Is
there a way to d
On Wed, Oct 13, 2004 at 08:36:50PM +0200, Pierre-Fr?d?ric Caillaud wrote:
>
> You can have your script make a query in the database to fetch the
> data types of the fields and then know which ones are to be transformed
> and how. The script would take as arguments a dump file and a
After some discussion on performance list, I guess this is back to a
general question :)
This is very simplified query of my real problem, but it should show the
way of the problems.
CREATE OR REPLACE FUNCTION ago(interval) RETURNS timestamp AS
'SELECT (now() - $1)::timestamp without time zone'
I think you want to EXECUTE that sql so it doesn't get compiled into the
function.
http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
- Ian
>>> Scott Cain <[EMAIL PROTECTED]> 10/14/04 8:01 AM >>>
OK, I've reworked my function and I can now create m
Gaetano Mendola wrote:
Patrick Fiche wrote:
Hi,
When I execute a function, I would like psql to show me only RAISE
NOTICE messages but not all function calls
Indeed, I currently get some messages that I don't care about :
* PL/pgSQL function "adm_user" line 321..
* CONTEXT: SQ
OK, I've reworked my function and I can now create my functions and
triggers; however, when I try to do a test update, I get the following
error:
ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "audit_update" line 7 at SQL statement
Which I think corresponds to 'a
"In addition, your original invocation of the postmaster command
must have a shorter ps status display than that provided by each
server process."
Yes, using PGDATA instead of the whole path eith the -D option worked:
now I can see the different status displays.
---(end of
On Thu, Oct 14, 2004 at 08:24:19AM -0600, Michael Fuhr wrote:
>
> As far as I can tell, for /usr/ucb/ps the show the replacement
> arguments, the sum of the lengths of the replacement arguments
> must be 2 or more greater than the sum of the lengths of the
> original arguments. I'm guessing that
On Thu, Oct 14, 2004 at 11:14:10AM +0100, Richard Huxton wrote:
> Leonardo Francalanci wrote:
> >I read "Chapter 23. Monitoring Database Activity" to monitor postgresql,
> >but on Solaris it doesn't work. I tried "/usr/ucb/ps", but it doesn't
> >work either (I only see the postmaster startup para
I wrote a function to sum arrays.
It works, but I had to cast the data pointer to int64 (because my arrays
are 'int8[]'):
int64* ptr1 = ARR_DATA_PTR(v1);
What if I want to write a more general function that adds values of 2
arrays of every int type? How could I do it?
Here is the function (if y
Hi Guys!
Anyone already made a importation from CTREE files ( files .dtc) to
PostgreSQL ?
I know that there is a "contrib" tool to import files from dbf to sql...
Any tip ?
--
Cheers ,
Rodrigo Miranda Carvalhaes
---(end of broadcast)---
TIP 6: Have
Stuart Bishop <[EMAIL PROTECTED]> writes:
> How much overhead is there in storing a timestamp with timezone as
> opposed to one without?
Exactly zero. You have a misconception about what the datatype really
does --- see other responses in this thread.
regards, tom lane
-
Neil Berkman <[EMAIL PROTECTED]> writes:
> Is there any way to remove a password, specifically from the postgres
> user?
You can set the field to NULL in pg_shadow, though I really wonder why
you'd bother, if you're not using password authentication.
regards, tom lane
---
Hi.
I understand that you are using NOD32 software. To correct install
postgres you must first uninstall IMON from NOD32.
IMON is som how intgerete with windows socket and somehow brake
communication with postgresql database.
Piotr Filipczuk
BARTKO, Zoltán wrote:
Hello, folks,
I am trying to ins
Leonardo Francalanci wrote:
I read "Chapter 23. Monitoring Database Activity" to monitor postgresql,
but on Solaris it doesn't work. I tried "/usr/ucb/ps", but it doesn't
work either (I only see the postmaster startup parameters). Isn't there
any other solution to see what postgresql instances a
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tom Lane wrote:
| Stuart Bishop <[EMAIL PROTECTED]> writes:
|
|>I'm trying to determine the best way of saying 'The current time in UTC
|>with no time zone information'.
|
|
| Isn't that a contradiction in terms?
Not at all - I want 'now' in UTC time wi
Magnus, et al.,
This is, what initdb -d says:
D:\tmp\data>initdb -d
Running in debug mode.
VERSION=8.0.0beta3
PGDATA=d:/tmp/data
share_path=c:/msys/1.0/share/postgresql
PGPATH=c:/msys/1.0/bin
POSTGRES_SUPERUSERNAME=postgresql
POSTGRES_BKI=c:/msys/1.0/share/postgresql/postgres.bki
POSTGRES_DESC
interface to postgresql. A mailing list would be ideal. I've
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
mes
I read "Chapter 23. Monitoring Database Activity" to monitor postgresql,
but on Solaris it doesn't work. I tried "/usr/ucb/ps", but it doesn't
work either (I only see the postmaster startup parameters). Isn't there
any other solution to see what postgresql instances are doing?
-
ruben wrote:
> Hi Gaetano:
>
> This procedure to recover data from a corrupted table should be
> documented somewhere... If it is, I could not find it!
First of all the table was not corrupted, the glitch was in another
subsystem.
The procedure is documented in the archives :-(
I agree with you but
Katsaros Kwn/nos wrote:
Well, actually no :) ! Thanks for the hint!
But just from curiosity, would the scenario I described work?
I mean is it possible for an SPI process to run in the background while
other SPI calls are made?
I don't think so, you're running in a backend process, so you'd need to
Well, actually no :) ! Thanks for the hint!
But just from curiosity, would the scenario I described work?
I mean is it possible for an SPI process to run in the background while
other SPI calls are made?
Ntinos Katsaros
On Thu, 2004-10-14 at 11:15, Richard Huxton wrote:
> Katsaros Kwn/nos wrote
Katsaros Kwn/nos wrote:
Hi,
I'm trying to add a -project specific- networking feature to my postgres
build (or database as function). What I want to do is to send a Query
instance (as a String-retrieved through an SPI function) to other
machines and (after they have executed it) to receive result t
Magnus, et al,
my server seems to play games with me, so here I am sending it again.
Zoltan
http://www.pobox.sk/ - spolahliva a bezpecna prevadzka
RE_ [GENERAL] beta3 winxp initdb problems.msg.eml
Description: Binary data
---(en
Hi,
I'm trying to add a -project specific- networking feature to my postgres
build (or database as function). What I want to do is to send a Query
instance (as a String-retrieved through an SPI function) to other
machines and (after they have executed it) to receive result tuples.
It's about a med
> Hello, folks,
>
> I am trying to install pgsql8 on winxp. I tried first to install "as
> is" with pginstaller beta2-dev3, no luck, it froze, switched off
> Nod32, froze a little later, ran through the list of services,
> switched off anything that seemed to be a firewall, no luck.
>
> So I comp
Hello, folks,
I am trying to install pgsql8 on winxp. I tried first to install "as
is" with pginstaller beta2-dev3, no luck, it froze, switched off
Nod32, froze a little later, ran through the list of services,
switched off anything that seemed to be a firewall, no luck.
So I compiled beta3 with
64 matches
Mail list logo
