Re: [SQL] CREATE USER in side a TRIGGER FUNCTION

On Thu, Apr 14, 2005 at 08:30:02PM -0700, shaun wrote: > > I want to put login and user managment into the database for security > reasons. I have a employee table. When I add a person to the employee > table I want to create them in the database and when I remove a person I > want to drop the

Re: [SQL] Getting the output of a function used in a where clause

Rod Taylor <[EMAIL PROTECTED]> writes: > You can force it with a subselect though: > SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) > as distance > from zipcodes) AS tab where distance <= $dist; The above will *not* stop zipdist from being run twice

Re: [SQL] can a function return a virtual table?

On Wed, Apr 13, 2005 at 03:44:25PM +0200, Kai Hessing wrote: > > This is the question i'm telling myself. It is because we don't really > delete table entries, just setting a status field to '-1'. So a valid > select would look like: SELECT xyz, abc FROM (SELECT * FROM tablex WHERE > status > -1);

Re: [SQL] SQL subquery (count distinct) - Any Ideas?

On Wed, 2005-04-13 at 18:28 +0100, Matt Fulford wrote: > I'm trying to write a query to return the number of different customers > called on a single day. The database has a table called 'user', a table > called 'caller_session' which references a 'user' record, and a table called > 'call' whic

Re: [SQL] Getting the output of a function used in a where clause

Thanks, Unfortunately, I think that solution requires the distance calculation to be executed twice for each record in the table. There are ~70K records in the table. Is the postgres query optimizer smart enough to only perform the calculation once? Bill -Original Message- From: Scott

Re: [SQL] Getting the output of a function used in a where clause

On Tue, 2005-04-12 at 23:08 -0700, Bill Lawrence wrote: > Thanks, > > Unfortunately, I think that solution requires the distance calculation to be > executed twice for each record in the table. There are ~70K records in the > table. Is the postgres query optimizer smart enough to only perform the

[SQL] tsearch2

Hi Oleg,   Still trying to get tsearch2 to work ... from archived message board it looks like our problem is that we don't have LC_CTYPE and LC_COLLATE established.   We're running 7.3.4 - are there any potential pitfalls if we set LC_CTYPE and LC_COLLATE (in conjunction with other settings?

Re: [SQL] SQL group select question

I have a table with a unique id, a name, a number and a value, for example: id name numb value -- - 1 tom 101000 2 dick 102000 3 harry 103000 4 dick 214000 5 harry 215000 6 harry 326000 As you can see, tom has 1 entry, dick has 2 and harry

[SQL] SQL subquery (count distinct) - Any Ideas?

I'm trying to write a query to return the number of different customers called on a single day. The database has a table called 'user', a table called 'caller_session' which references a 'user' record, and a table called 'call' which references a 'caller_session'. This is my current attempt:

[SQL] can a function return a virtual table?

This is the question i'm telling myself. It is because we don't really delete table entries, just setting a status field to '-1'. So a valid select would look like: SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); It would be much nicer to have to write something like: SELECT xyz, abc

[SQL] CREATE USER in side a TRIGGER FUNCTION

Hi I want to put login and user managment into the database for security reasons. I have a employee table. When I add a person to the employee table I want to create them in the database and when I remove a person I want to drop the person from the database also. How do you do it and what is

Re: [SQL] How to Port Oracle's user defined "Package" into Postgres 8.0.1.

hi, Dinesh Pandey wrote, On 4/11/2005 15:39: Hi folks, Can any one give me an idea about: How to Port Oracle's user defined "Package" into Postgres 8.0.1. there is no Package in postgresql. You have to rewrite the logic, extract the functions from Package and recreate them for postgres. But you

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

Alvaro Herrera wrote: It is a Postgres limitation as well. We _could_ make the server "really start the transaction" at the point the first query is issued instead of when the BEGIN is issued. And also, really finish the transaction right after the last statement is executed, instead of waiting u

Re: [SQL] Function declaration

Alex, What happens if you declare your function as: function(text,text) or whatever your datatypes for each argument are supposed to be and then, within your function, test whether the arguments are NULL? I haven't tried it, so I can't say for sure, but it seems like you should be able to pass a

[SQL] Function declaration

Is there a possiblity to declare functions with optional parameters i.e. in case some parameters are not passed to the function, then some kind of default value is assigned to the function? regards, alex -- The mind is essential -- http://essentialmind.com/ ---(end of br

Re: [SQL] tsearch2

Dan, I don't remember what the probem you have ? Oleg On Mon, 18 Apr 2005, Dan Feiveson wrote: Hi Oleg, Still trying to get tsearch2 to work ... from archived message board it looks like our problem is that we don't have LC_CTYPE and LC_COLLATE established. We're running 7.3.4 - are there any pote

Re: [SQL] About "Alter table... alter column.. TYPE ... "

> To alter table column from varchar(32) to date. "Alter table" command > does not seem to work: > > alter table test alter column col type date ; > ERROR: column "col1" cannot be cast to type "date" Alter table will not automatically throw away information. That is, in cases where it believes

[SQL] About "Alter table... alter column.. TYPE ... "

Hello, To alter table column from varchar(32) to date. "Alter table" command does not seem to work: alter table test alter column col type date ; ERROR: column "col1" cannot be cast to type "date" Tks, Emi ---(end of broadcast)--- TIP 7: don't for

Re: [SQL] User Defined Functions Errors

On Mon, Apr 18, 2005 at 10:32:26PM +0400, A. Kulikov wrote: > > How to I return an error from inside a user defined function? Use RAISE. See "Errors and Messages" in the PL/pgSQL documentation. http://www.postgresql.org/docs/8.0/interactive/plpgsql-errors-and-messages.html -- Michael Fuhr http

[SQL] User Defined Functions Errors

How to I return an error from inside a user defined function? For example the following: CREATE or REPLACE FUNCTION drop_node (integer) RETURNS text AS ' DECLARE mleft INTEGER; mright INTEGER; BEGIN -- Check if the desired node exists SELECT lft, rgt FROM structure WHERE id = $1 INTO mleft, m

Re: [SQL] [GENERAL] 'Select INTO" in Execute (dynamic query )

"Dinesh Pandey" <[EMAIL PROTECTED]> writes: > What's wrong with this code (ERROR: syntax error at or near "INTO" at > character 8)? You can't use plpgsql's SELECT INTO in an EXECUTE'd command, because SELECT INTO means something entirely different to the main SQL engine. The usual workaround is

[SQL] 'Select INTO" in Execute (dynamic query )

Hi   What’s wrong with this code (ERROR:  syntax error at or near "INTO" at character 8)?   Problem: I want to put A1, A2 values in two variables vara, varb.   CREATE OR REPLACE FUNCTION test(text) RETURNS VARCHAR AS $$ Declare   vara    VARCHAR(10) :='';   varb  

Re: [SQL] outer join in ms query

Or add a view to your PG database, and simply issue a select from Excel... That faster and easier to maintain that any code you can write client-side... -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Jeff Eckermann Envoyé : lundi, 18. avril 2005 16:51 À

Re: [SQL] outer join in ms query

MS Query is crippled. If you want to do much from Excel, you will need to write code. But it's not difficult. ADO is a good choice for this. "gad renert via DBMonster.com" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello, I am a novice with SQL, I have 2 tables > (transfer

Re: [SQL] user connection over tcp

hello again. ok. i can connect over tcp!!! this works! my problem is that i only can connect with trust! if i use password i cant connect. with trustmode i need the right password for the user! frank -- Original-Nachricht -- Von: "Frank Habermann" <[EMAIL PROTECTED]> An: "pgsql-sql@postgresql.

Re: [SQL] How to add 1 hour in a date or time stamp?

O Dinesh Pandey έγραψε στις Apr 18, 2005 : > > > How to add 1 hour in a date or time stamp? # SELECT now() + '1 hour'::interval; > > Regards > Dinesh Pandey > > > > -- > > > > > > -- -Achilleus ---

Re: [SQL] How to add 1 hour in a date or time stamp?

On Monday 18 April 2005 08:38 am, Dinesh Pandey wrote: > How to add 1 hour in a date or time stamp? timestamp + '1 hour'::interval is one way. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Dri

Re: [SQL] user connection over tcp

Edit "postgres.conf" and "pg_hba.conf" to access database from a remote machine Edit "postgres.conf": -- listen_addresse='*' Edit "pg_hba.conf": -- hostall all 10.1.11.54 255.255.255.0 trust

Re: [SQL] How to add 1 hour in a date or time stamp?

Dinesh,   The documentation is very helpful for this topic.  Typing 'add 1 hour timestamp' into the search box at:   http://www.postgresql.org/docs/8.0/interactive/index.html   yields the following page as the first hit:   http://www.postgresql.org/docs/8.0/static/functions-datetime.html   Sea

[SQL] How to add 1 hour in a date or time stamp?

  How to add 1 hour in a date or time stamp? Regards Dinesh Pandey --  

Re: [SQL] user connection over tcp

Did you start the postmaster with -i to allow tcp connections? What is the error that you get when you try to connect? Sean - Original Message - From: "Frank Habermann" <[EMAIL PROTECTED]> To: Sent: Monday, April 18, 2005 9:31 AM Subject: [SQL] user connection over tcp hello! i have

[SQL] user connection over tcp

hello! i have some problems to understand the manual with pg_hba.conf. i use a tcp connection with phppgadmin to connect to the db. in my pg_hba.conf i say for this connection trust! in phppgadmin i need the for my user the right password to connect. but the manual says that every password will

[SQL] btree and is null in a static expression

Hi, I've written a function but I don't understand the the plan, the planner makes. If variables are replaced, the function looks like that: select a,b,c from "Tbl1" where (a='454') or ('454' is null); a has got an btree-Index. explain verbose tells me that Postgres wants to do a SEQSCAN If th