Re: [SQL] columnar format
> > i would like to ask for the sql statement to the output: > > ITEM DESC Jan Feb Mar Apr ... Sep Total > xx 999 999 999 999 ... 999 9,999 > > where "Jan" column is sum of all "x" items purchased on Jan, "Feb" column as > sum of Feb purchases, and so on up to "Sep", and "Total" as the total from > "Jan" to "Sep" (as in the case here). the numbers may/may not be formatted, > and the period varies, depending on the range. > Search the archives for pivot tables resp. crosstab and/or have a look at $PGSQLD/contrib/tablefunc/README.tablefunc Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Setuid functions
Hi all, How can I set a function as setuid ? I take a look at the documetation, on Reference Manual and the only reference I saw to it was on SET SESSION AUTHORIZATION. Thanks in advance. Luis Sousa ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Setuid functions
Luis Sousa wrote:
How can I set a function as setuid ?
I take a look at the documetation, on Reference Manual and the only
reference I saw to it was on SET SESSION AUTHORIZATION.
See:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createfunction.html
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
RETURNS rettype
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]
[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER
SECURITY INVOKER indicates that the function is to be executed with
the privileges of the user that calls it. That is the default. SECURITY
DEFINER specifies that the function is to be executed with the
privileges of the user that created it.
The key word EXTERNAL is present for SQL compatibility but is
optional since, unlike in SQL, this feature does not only apply to
external functions.
HTH,
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Datatype conversion help
Suppose I have an integer between 0 and 99 and I want to covert it to string, and pad leading zeros if neccessary. For example, 1 => 01 10 => 10 I've tried to_char(in_val, '99'), and that returns a string that is two charecters, but there isn't a leading zero incase I have the number 2 as input. Any ideas? Thanks. Yasir ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Datatype conversion help
You want to use: nachbaur=# select to_char(5, '00'); to_char - 05 (1 row) By using "0", you indicate you want leading zeros. See http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html for more information. On Tuesday 08 July 2003 01:07 pm, Yasir Malik wrote: > Suppose I have an integer between 0 and 99 and I want to covert it to > string, and pad leading zeros if neccessary. For example, > 1 => 01 > 10 => 10 > > I've tried to_char(in_val, '99'), and that returns a string that is two > charecters, but there isn't a leading zero incase I have the number 2 as > input. Any ideas? Thanks. > Yasir > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Oh no, not again." ---(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] Datatype conversion help
Thank you so much! But my problem is that when I do to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, '') where mn, dy, and yr are ints, is that the output has a space after the the dash. For example, I get 07- 25- 1994 instead of what I want: 07-25-1994 Thanks, Yasir On Tue, 8 Jul 2003, Richard Rowell wrote: > Date: 08 Jul 2003 15:21:33 -0500 > From: Richard Rowell <[EMAIL PROTECTED]> > To: Yasir Malik <[EMAIL PROTECTED]> > Subject: Re: [SQL] Datatype conversion help > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > I've tried to_char(in_val, '99'), and that returns a string that is two > > select to_char(9,'00'); > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Datatype conversion help
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE See the pattern modifier "FM". From the docs: "FM prefix - fill mode (suppress padding blanks and zeroes)" On Tuesday 08 July 2003 01:28 pm, Yasir Malik wrote: > Thank you so much! But my problem is that when I do > to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > '') > > where mn, dy, and yr are ints, is that the output has a space after the > the dash. For example, I get > 07- 25- 1994 > > instead of what I want: > 07-25-1994 > > Thanks, > Yasir > > On Tue, 8 Jul 2003, Richard Rowell wrote: > > Date: 08 Jul 2003 15:21:33 -0500 > > From: Richard Rowell <[EMAIL PROTECTED]> > > To: Yasir Malik <[EMAIL PROTECTED]> > > Subject: Re: [SQL] Datatype conversion help > > > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > > I've tried to_char(in_val, '99'), and that returns a string that is two > > > > select to_char(9,'00'); > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "He expanded his chest to make it totally clear that here was the sort of man you only dared to cross if you had a team of Sherpas with you. " ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Datatype conversion help
Yasir, If this is a date you're playing with, simply use: to_char( , 'MM-DD-' ) to get what you want. -- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -Original Message- > From: Yasir Malik [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2003 1:29 PM > To: [EMAIL PROTECTED] > Subject: Re: [SQL] Datatype conversion help > > > Thank you so much! But my problem is that when I do > to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > '') > > where mn, dy, and yr are ints, is that the output has a space > after the > the dash. For example, I get > 07- 25- 1994 > > instead of what I want: > 07-25-1994 > > Thanks, > Yasir > > On Tue, 8 Jul 2003, Richard Rowell wrote: > > > Date: 08 Jul 2003 15:21:33 -0500 > > From: Richard Rowell <[EMAIL PROTECTED]> > > To: Yasir Malik <[EMAIL PROTECTED]> > > Subject: Re: [SQL] Datatype conversion help > > > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > > I've tried to_char(in_val, '99'), and that returns a > string that is two > > > > select to_char(9,'00'); > > > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Datatype conversion help
Yes, Mr. Nachbaur helped me out. Thanks. I don't think I can do to_char(, 'MM-DD-) because the date fields are originally stored as separate integers in my schema (they have to be that way). I still can't understand why the extra space was added after the dash. It just made my life more miserable. Yasir On Tue, 8 Jul 2003, David Olbersen wrote: > Date: Tue, 8 Jul 2003 14:02:55 -0700 > From: David Olbersen <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [SQL] Datatype conversion help > > Yasir, > > If this is a date you're playing with, simply use: > > to_char( , 'MM-DD-' ) > > to get what you want. > > -- > David Olbersen > iGuard Engineer > St. Bernard Software > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > > > -Original Message- > > From: Yasir Malik [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, July 08, 2003 1:29 PM > > To: [EMAIL PROTECTED] > > Subject: Re: [SQL] Datatype conversion help > > > > > > Thank you so much! But my problem is that when I do > > to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, > > '') > > > > where mn, dy, and yr are ints, is that the output has a space > > after the > > the dash. For example, I get > > 07- 25- 1994 > > > > instead of what I want: > > 07-25-1994 > > > > Thanks, > > Yasir > > > > On Tue, 8 Jul 2003, Richard Rowell wrote: > > > > > Date: 08 Jul 2003 15:21:33 -0500 > > > From: Richard Rowell <[EMAIL PROTECTED]> > > > To: Yasir Malik <[EMAIL PROTECTED]> > > > Subject: Re: [SQL] Datatype conversion help > > > > > > On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: > > > > I've tried to_char(in_val, '99'), and that returns a > > string that is two > > > > > > select to_char(9,'00'); > > > > > > > ---(end of > > broadcast)--- > > TIP 8: explain analyze is your friend > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Logging select statements
Hi, I have an application where user's can view records in a short form with their first select and view a long form with a second select. The first view I term an impression. The second view I term a click. I'd like to log the impression's and click's. I'm wondering which is the most effiecient way to do this. I know I can do it in the application, PHP, by looping through the result set and inserting into a logging table but am wondering if it quicker to write a rule or trigger so that each individual select is logged into a logging table as it's selected. For example: If I have a table of 3000 row's and the user submits a query which retrieve's 100 rows. In the first senario I could loop through the 100, using a language PHP or Perl, and make 100 inserts after the first select is complete. Thus 1 select plus 100 inserts. Can you see a way to do this all in SQL that would be better/faster/more efficient without using PHP/Perl ? Many thanks Regards Rudi. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] executing a function
Hi, i doubt if this should 've gone to the novice group, but it's all sql i have a function that returns a setof rows from a table testtab : CREATE FUNCTION "public"."gettesttab" () RETURNS SETOF public.testtab AS' select * from TestTab; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; "volatile" etc. was added by the EMS PostGreSql Manager. i try to execute this function but a call "select * from gettesttab();" or "select gettesttab();" return the error ERROR: syntax error at or near "select" the postmaster window also logs the erros: ERROR: syntax error at or near "select"WARNING: plpgsql: ERROR during compile of gettesttab near line 1 can simeone please help me out with this. thanks in advance regards-pawan
Re: [SQL] executing a function
Hi Pawan, CREATE FUNCTION "public"."gettesttab" () RETURNS SETOF public.testtab AS' select * from TestTab; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; You want LANGUAGE 'sql' Cheers Matthew. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Logging select statements
Thats why people who want entreprise apps must use enterprise frameworks. In J2EE for instance you could use LOG4J which is sorta equivalent of syslog for java. See if there is a logging module for PHP. PgSQL has no clue of who the user is. I dont think delegating this logging task to pgSQL is a good idea. Dont get me wrong, I like and use php myself, but only when i know the exact limits of how far the specific project will go in the future. On Wed, 9 Jul 2003, Rudi Starcevic wrote: > Hi, > > I have an application where user's can view records in a short form with > their first select > and view a long form with a second select. > The first view I term an impression. > The second view I term a click. > > I'd like to log the impression's and click's. > I'm wondering which is the most effiecient way to do this. > > I know I can do it in the application, PHP, by looping through the > result set and inserting into a logging table but > am wondering if it quicker to write a rule or trigger so that each > individual select is logged into a logging table > as it's selected. > > For example: > If I have a table of 3000 row's and the user submits a query which > retrieve's 100 rows. > In the first senario I could loop through the 100, using a language PHP > or Perl, and make 100 inserts after the first select is complete. > Thus 1 select plus 100 inserts. > > Can you see a way to do this all in SQL that would be better/faster/more > efficient without using PHP/Perl ? > > Many thanks > Regards > Rudi. > > > > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Logging select statements
Thanks Achilleus, I know there's a couple of ways I could do this. In my first email I can see a senario of 1 select plus 100 inserts. Another may be 1 select plus 1 insert. For example; In a table of 3000 rows a user submits a query which returns 100 rows. I could loop through the result set and build a string of id's ( 1,2,5,7,8,9,44,22 etc ) and make one insert into a logging table of the entire string. At a later time, say once every 24 hours, I could select each row of id's and make further inserts into another logging table. Eg. I extract 1 row with a string of 100 key's and make 100 inserts into a second log table. I could even then use a 'count(id), date GROUP BY date' select to add a single row to a further logging table which has one row per id with a count of all impressions/click's for that day. Thanks I'm just trying to explore way's of logging hits and maximize performance for the end user. I hope I explained all that OK and didn't ramble to much. Cheers Rudi. Achilleus Mantzios wrote: Thats why people who want entreprise apps must use enterprise frameworks. In J2EE for instance you could use LOG4J which is sorta equivalent of syslog for java. See if there is a logging module for PHP. PgSQL has no clue of who the user is. I dont think delegating this logging task to pgSQL is a good idea. Dont get me wrong, I like and use php myself, but only when i know the exact limits of how far the specific project will go in the future. On Wed, 9 Jul 2003, Rudi Starcevic wrote: Hi, I have an application where user's can view records in a short form with their first select and view a long form with a second select. The first view I term an impression. The second view I term a click. I'd like to log the impression's and click's. I'm wondering which is the most effiecient way to do this. I know I can do it in the application, PHP, by looping through the result set and inserting into a logging table but am wondering if it quicker to write a rule or trigger so that each individual select is logged into a logging table as it's selected. For example: If I have a table of 3000 row's and the user submits a query which retrieve's 100 rows. In the first senario I could loop through the 100, using a language PHP or Perl, and make 100 inserts after the first select is complete. Thus 1 select plus 100 inserts. Can you see a way to do this all in SQL that would be better/faster/more efficient without using PHP/Perl ? Many thanks Regards Rudi. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
