Re: [SQL] "record" datatype - plpgsql
Hi Brian; Assuming "NEW" has been declared as foo%rowtype, you can access the columns thus NEW.xxx where xxx is the column name HTH. George - Original Message - From: "Brian Knox" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 29, 2003 11:11 AM Subject: [SQL] "record" datatype - plpgsql > Given a variable of the "record" data type in pl/pgsql, is it possible to > get the names of the columns ( attributes ) of that record? > > eg, given record "NEW" for table "foo", is there a way to get information > concerning the columns that make up that record? > > Brian Knox > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > ---(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] "record" datatype - plpgsql
Brian, You can also use the "record" type as well in the same way. George - Original Message ----- From: "George Weaver" <[EMAIL PROTECTED]> To: "Brian Knox" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, May 29, 2003 2:01 PM Subject: Re: [SQL] "record" datatype - plpgsql > Hi Brian; > > Assuming "NEW" has been declared as foo%rowtype, you can access the > columns thus > > NEW.xxx where xxx is the column name > > HTH. > George > > - Original Message - > From: "Brian Knox" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, May 29, 2003 11:11 AM > Subject: [SQL] "record" datatype - plpgsql > > > > Given a variable of the "record" data type in pl/pgsql, is it possible to > > get the names of the columns ( attributes ) of that record? > > > > eg, given record "NEW" for table "foo", is there a way to get information > > concerning the columns that make up that record? > > > > Brian Knox > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
It works in 7.3.2. George - Original Message - From: Elielson Fontanezi To: pgsql-general ; pgsql-sql Sent: Wednesday, July 30, 2003 10:52 AM Subject: [SQL] ALTER TABLE ... DROP CONSTRAINT Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
[SQL] Strange behavior with timestamptz
Hi Everyone, I have a situation where two tables have a "Created" field defined as follows: table seedlot "created timestamptz DEFAULT now(), " table transaction"created timestamptz NOT NULL DEFAULT now()," The transaction table records when a seedlot record is created, with both events happening in the same plpgsql function. The seedlot record is created first, with the transaction table being updated later in the procedure. Seedlot 153 was received on August 11 with the following result: base=# select created from seedlot where syslotid=153; created 2003-11-08 12:13:39-06(1 row) base=# select created from transaction where syslotid=153 and transactiontypeid=22; created 2003-08-11 12:13:39-05(1 row) Does anyone have any idea why the default for seedlot recorded the time with the day and month switched, resulting in the seedlot record being stamped Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003? In the same function a number of other records are created and timestamped. In all cases where the table definition statement does not include NOT NULL, the time was stamped with the day and month reversed. However! Two of the tables define created thus: "created timestamptz DEFAULT now() NOT NULL," but still ended up with the day and month being switched. The transaction record is the last one in the procedure to be created. I am using version 7.3.2. I hope I'm not missing something obvious... Thanks for your help, George
Re: [SQL] Strange behavior with timestamptz
Hi Tom, I have written both the application and the PostgreSQL procedures (still in development). All the timestamps I referred to are created by default when the stored procedure is invoked. The problem may be due to some inconsistency in how PostgreSQL is interpreting what the operating system (Windows XP) is supplying. While I have not been able to recreate the problem directly, another aspect of the process does show an inconsistency. In the application the user enters the date the product was received (which may be different from the date the record is created). This is passed to the stored procedure as a parameter of type date, and is inserted into a date field (datereceived). By changing the system date and running the application the following occurred: Date received entered as August 11, 2003: ? me.datetimepicker1.value #8/11/2003 8:18:28 PM# base=# select datereceived, created from receiving where receivingid=56; datereceived | created --+-- 2003-11-08 | 2003-08-25 20:20:55.41425-05 (1 row) Date received entered as August 25, 2003: ? me.datetimepicker1.value #8/25/2003 8:22:37 PM# base=# select datereceived, created from receiving where receivingid=57; datereceived | created --+-- 2003-08-25 | 2003-08-25 20:22:39.68625-05 (1 row) base=# show datestyle; DateStyle --- ISO with US (NonEuropean) conventions (1 row) In both cases the month is being sent to the stored procedure first, but in the first instance (month < 13) it is being interpreted as the day. George - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 25, 2003 3:59 PM Subject: Re: [SQL] Strange behavior with timestamptz > "George Weaver" <[EMAIL PROTECTED]> writes: > > Does anyone have any idea why the default for seedlot recorded the time wit= > > h the day and month switched, resulting in the seedlot record being stamped= > > Nov 8, 2003 while the transaction was stamped correctly as Aug 11, 2003? > > It's really not possible to believe that both of those were loaded from > the defaults you show. now() doesn't ever break down the system clock > value into day/month/year --- it just takes the system clock time in > seconds-since-epoch and adds a constant to get the right zero offset. > So there's no credible mechanism for now() to make such a mistake. > > I think that your client software supplied a value for one field and > didn't supply a value for the other, and the supplied value was provided > in the wrong DateStyle. > > regards, tom lane > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Strange behavior with timestamptz
Hi Tom, Not believing that PostgreSQL would be less consistent than Microsoft :-), I spent some time looking at how the application actually was transferring the date to the database procedure. When the datereceived parameter was defined as type Date, it was actually arriving at the procedure as "11-08-2003" and "25-08-2003" in spite of showing up as indicated below. When I redefined the datereceived parameter as type OdbcDate, it arrived correctly at the procedure as "2003-08-11" and "2003-08-25". Checking further, this appears to result from the computer's time settings (English - Canada) which I know realize use the European format. So it looks like this problem arose due to my inexperience. (Nonetheless I am still perplexed by the fact that the default values were assigned inconsistently as explained earlier and will try to puzzle this one through as well). Sorry for the confusion. George - Original Message - From: "George Weaver" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 25, 2003 9:51 PM Subject: Re: [SQL] Strange behavior with timestamptz > Hi Tom, > > I have written both the application and the PostgreSQL procedures (still in > development). All the timestamps I referred to are created by default when > the stored procedure is invoked. The problem may be due to some > inconsistency in how PostgreSQL is interpreting what the operating system > (Windows XP) is supplying. While I have not been able to recreate the > problem directly, another aspect of the process does show an inconsistency. > > In the application the user enters the date the product was received (which > may be different from the date the record is created). This is passed to > the stored procedure as a parameter of type date, and is inserted into a > date field (datereceived). By changing the system date and running the > application the following occurred: > > Date received entered as August 11, 2003: > > ? me.datetimepicker1.value > #8/11/2003 8:18:28 PM# > > base=# select datereceived, created from receiving where receivingid=56; > datereceived | created > --+-- > 2003-11-08 | 2003-08-25 20:20:55.41425-05 > (1 row) > > Date received entered as August 25, 2003: > > ? me.datetimepicker1.value > #8/25/2003 8:22:37 PM# > > base=# select datereceived, created from receiving where receivingid=57; > datereceived | created > --+-- > 2003-08-25 | 2003-08-25 20:22:39.68625-05 > (1 row) > > base=# show datestyle; >DateStyle > --- > ISO with US (NonEuropean) conventions > (1 row) > > In both cases the month is being sent to the stored procedure first, but in > the first instance (month < 13) it is being interpreted as the day. > > George > > - Original Message - > From: "Tom Lane" <[EMAIL PROTECTED]> > To: "George Weaver" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, August 25, 2003 3:59 PM > Subject: Re: [SQL] Strange behavior with timestamptz > > > > "George Weaver" <[EMAIL PROTECTED]> writes: > > > Does anyone have any idea why the default for seedlot recorded the time > wit= > > > h the day and month switched, resulting in the seedlot record being > stamped= > > > Nov 8, 2003 while the transaction was stamped correctly as Aug 11, > 2003? > > > > It's really not possible to believe that both of those were loaded from > > the defaults you show. now() doesn't ever break down the system clock > > value into day/month/year --- it just takes the system clock time in > > seconds-since-epoch and adds a constant to get the right zero offset. > > So there's no credible mechanism for now() to make such a mistake. > > > > I think that your client software supplied a value for one field and > > didn't supply a value for the other, and the supplied value was provided > > in the wrong DateStyle. > > > > regards, tom lane > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Help me
Hi Yaroslav, You must set the language as: LANGUAGE 'plpgsql'; Regards, George - Original Message - From: Yaroslav Ulyanov To: [EMAIL PROTECTED] Sent: Thursday, September 04, 2003 2:46 AM Subject: [SQL] Help me Hello I cannot write new function with local variables (see image in attachment). That I do wrong? Best regards,Yaroslav Ulyanov[EMAIL PROTECTED] ---(end of broadcast)---TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [GENERAL] plPGSQL bug in function creation
I had the same success using 7.3.2 with Cygwin:
e=# SELECT functest1('A','B'), functest1(null,'B'), functest2('A','B'),
functest2(null,'B');
functest1 | functest1 | functest2 | functest2
---+---+---+---
A | B | A | B
(1 row)
e=# select version();
version
--
PostgreSQL 7.3.2 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927
(prerelease)
(1 row)
George
- snip -
> Not sure whether this is because of the change 7.3.1->7.3.2 or cygwin vs
> linux. Don't have a copy of 7.3.1 to check against, sorry. Perhaps check
the
> changes list to see if it mentions something like this.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---(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
>
---(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
[SQL] Capturing pgsql ERRORS/NOTICES to file
I am in the process of creating a batch file that will update some functions in a database for a remote user similar to: psql -o output dbname < functionupdate.sql Is there any way to save any ERROR and NOTICE messages to a file? The -o option doesn't capture this information. Thanks, George
Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
Hi Josh, Thanks for the reply. What I am trying to achieve is to have errors go to a file, rather than show up on the screen. Is this possible? George - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September 26, 2003 1:33 PM Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file > George, > > > I am in the process of creating a batch file that will update some > > functions in a database for a remote user similar to: > > > > psql -o output dbname < functionupdate.sql > > > > Is there any way to save any ERROR and NOTICE messages to a file? > > > > The -o option doesn't capture this information. > > You have to use command shell redirects. > > For example, I commonly do in bash > psql -o output dbname < functionupdate.sql >out.dump > ... which sends all the command responses to a file, allowing me to read only > the errors on the screen. > > See a guide to your shell for more creative redirection. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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] Capturing pgsql ERRORS/NOTICES to file
Hi Wei,
I hadn't tried that, and it did the trick!
Thank you!
George
- Original Message -
From: "Wei Weng" <[EMAIL PROTECTED]>
To: "George Weaver" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, September 26, 2003 2:16 PM
Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
> George:
>
> Have you tried psql {whatever operations} 2> error_output ?
>
> (for Bash)
>
> Thanks
>
> Wei
>
>
> On Fri, 26 Sep 2003, George Weaver wrote:
>
> > Hi Josh,
> >
> > Thanks for the reply.
> >
> > What I am trying to achieve is to have errors go to a file, rather than
show
> > up on the screen.
> >
> > Is this possible?
> >
> > George
> >
> > - Original Message -
> > From: "Josh Berkus" <[EMAIL PROTECTED]>
> > To: "George Weaver" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Friday, September 26, 2003 1:33 PM
> > Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
> >
> >
> > > George,
> > >
> > > > I am in the process of creating a batch file that will update some
> > > > functions in a database for a remote user similar to:
> > > >
> > > > psql -o output dbname < functionupdate.sql
> > > >
> > > > Is there any way to save any ERROR and NOTICE messages to a file?
> > > >
> > > > The -o option doesn't capture this information.
> > >
> > > You have to use command shell redirects.
> > >
> > > For example, I commonly do in bash
> > > psql -o output dbname < functionupdate.sql >out.dump
> > > ... which sends all the command responses to a file, allowing me to
read
> > only
> > > the errors on the screen.
> > >
> > > See a guide to your shell for more creative redirection.
> > >
> > > --
> > > Josh Berkus
> > > Aglio Database Solutions
> > > San Francisco
> > >
> > > ---(end of
broadcast)---
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]
g)
> > >
> >
> >
> > ---(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
> >
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Problem with Escape charactor
Kumar,
Have you tried
EXECUTE 'update "WATS".action_plan_master set
rec_deleted_flag = \'Y\' WHERE action_plan_id IN ('|| p_action_plan_ids ||
')';^
^
HTH,
George
- Original Message -
From:
Kumar
To: psql
Sent: Tuesday, October 07, 2003 7:57
AM
Subject: [SQL] Problem with Escape
charactor
Dear Friends,
I am working with Postgres 7.3.4 on RH Linux 7.2 . I am
executing a dynamic query inside a PL/pgSQL procedure and I am having the
following problem.
While a line in the PL/pgSQL function is like the
following
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = 'Y' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
I got the following error.
ERROR: parser: parse error at
or near "Y" at character 68
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| 'Y' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
ERROR: Attribute "y" not
found
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| \'Y\' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
WARNING: plpgsql: ERROR during
compile of sp_del_met_001 near line 47 ERROR:
unterminated string
How can I specify a string charactor, as the PgAdmin3 is not
using double quotes for Strings. Anyone pls shed some light.
Regards
Kumar
Re: [SQL] Bad encoding in URL
>From the manual:
replace(string text, from text, to text) text Replace all occurrences
in string of substring from with substring to replace('abcdefabcdef', 'cd',
'XX') abXXefabXXef
HTH.
George
- Original Message -
From: "HR" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 06, 2003 5:45 AM
Subject: [SQL] Bad encoding in URL
> Hi
>
> I have some URLs in a database but some of the URL contain %3A and so on..
>
> Is there an easy way of converting '%3A' to ':' ???
>
> I cannot find any replace(String str, String str) among the string
> functions.
>
> ThankYou.
>
>
>
> ---(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
>
---(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] Possible to have array as input paramter for a function?
Hi Kumar, It is possible to pass an array to a PL/pgSQL function, but I believe you must specify the length of the array (at least doing so works for me). E.g. "varchar(20)". Regards, George - Original Message - From: Kumar To: psql Sent: Wednesday, October 08, 2003 5:47 AM Subject: [SQL] Possible to have array as input paramter for a function? Dear Friends, I am working with Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
Re: [SQL] Possible to have array as input paramter for a function?
Hi Kumar, Looks like I got up too early this morning - please ignore my previous reply re: varchar(30) (I was looking at the wrong function :-( ). I do use arrays in Pl/pgSQL functions, and have defined them as you did, e.g. varchar[], which does not return an error. What version of PostgreSQL are you using? I am running 7.3.2. Regards, George - Original Message - From: Kumar To: psql Sent: Wednesday, October 08, 2003 5:47 AM Subject: [SQL] Possible to have array as input paramter for a function? Dear Friends, I am working with Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
Re: [SQL] Object description at Client Window
Kumar,
pg_class.relname is type "name". You are trying to compare it to p_tablename
which is type "varchar". Try changing your function definition to:
CREATE OR REPLACE FUNCTION public.desc_table(name)
HTH
George
SNIP
> CREATE OR REPLACE FUNCTION public.desc_table(varchar)
> RETURNS refcursor AS
> 'DECLARE
>
> ref REFCURSOR ;
> p_tablename ALIAS FOR $1;
>
> BEGIN
> OPEN ref FOR
> SELECT a.attname,
> format_type(a.atttypid, a.atttypmod),
> a.attnotnull,
> a.atthasdef,
> a.attnum
> FROM pg_class c, pg_attribute a
> WHERE c.relname = p_tablename
> AND a.attnum > 0
> AND a.attrelid = c.oid
> ORDER BY a.attnum;
>
> RETURN ref;
> END;'
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> While trying to execute this
> select desc_table('companies');
>
> I got the following error.
> WARNING: Error occurred while executing PL/pgSQL function desc_table
> WARNING: line 7 at open
>
> ERROR: Unable to identify an operator '=' for types 'name' and 'character
> varying'
> You will have to retype this query using an explicit cast
>
SNIP
---(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] Dynamic Query for System functions - now()
Kumar, What about this: EXECUTE 'select now()+ interval \' || to_char(3,\'9\') || \'month\' '; George - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: "Christoph Haller" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, November 10, 2003 5:57 AM Subject: Re: [SQL] Dynamic Query for System functions - now() > Dear Christoph Haller, > > The code that u sent is not working > test=> EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; > ERROR: parser: parse error at or near "'select now()+ interval\''" at > character 9 > test=> > > Also I tried just to run the now() function with a dynamic query, I got the > following error. Please correct me. > > test=> select now(); > now > --- > 2003-11-10 17:06:36.783779+00 > (1 row) > > test=> execute 'select now()'; > ERROR: parser: parse error at or near "'select now()'" at character 9 > test=> > > > Regards > kumar > > - Original Message - > From: "Christoph Haller" <[EMAIL PROTECTED]> > To: ""Kumar"" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, November 10, 2003 5:07 PM > Subject: Re: [SQL] Dynamic Query for System functions - now() > > > > > > > > Dear Friends, > > > > > > I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I am trying to > generate= > > > a dynamic query to fetch the next month interval. > > > > > > select now()+ interval'1 month'; -- This is working fine. > > > > > > I wanna dynamically assign the interval number. i,e --> select now()+ > inter= > > > val'n month'; > > > > > > For this I wanted to write a dynamic query. > > > EXECUTE 'select now()+ interval\'' || 3|| 'month\''; > > > Error > > > ERROR: parser: parse error at or near "'select now()+ interval\''" > at = > > > character 9 > > > > > > Help help me with this. I wanted to use this query inside a PLpgSQL > functio= > > > n. > > > > > > Regards > > > Kumar > > > > > You'd probably want to use the to_char() function like this > > EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; > > > > The || operator is the string concatination operator. > > > > Regards, Christoph > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Looks are important
Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad? Thanks in advance, George
Re: [SQL] Looks are important
Title: Message Hi Louise, Josh, Thanks for the suggestions. What I'm trying to accomplish is to have a space between no and kind. Length(no) can vary. I would like all the kinds to line up evenly when displayed, with a space between no and kind. But when I RPAD no (to try and get an even starting point for kind), the ' 's are not quite the same width as an ordinary number or letter. Thus the physical display length of "30 characters" (padded) can vary from row to row. The result is that the kinds don't necessary line up neatly. I need to concatenate the two as they are being displayed as one column in a drop down combobox. Is what I'm trying to do possible??? George - Original Message - From: Louise Cofield To: 'George Weaver' ; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:19 PM Subject: RE: [SQL] Looks are important Try the TRIM function or the LTRIM function: SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea WHERE tablea.kind = tableb.kind Louise -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George WeaverSent: Wednesday, November 12, 2003 3:12 PMTo: [EMAIL PROTECTED]Subject: [SQL] Looks are important Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad? Thanks in advance, George
Re: [SQL] Looks are important
Hi Tom, Switching to a fixed-width font did the trick. Thanks for the help. George - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "George Weaver" <[EMAIL PROTECTED]> Cc: "Josh Berkus" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Louise Cofield" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 9:31 PM Subject: Re: [SQL] Looks are important > "George Weaver" <[EMAIL PROTECTED]> writes: > > ... the ' 's are not quite the same width as= > > an ordinary number or letter. Thus the physical display length of "30 cha= > > racters" (padded) can vary from row to row. The result is that the kinds do= > > n't necessary line up neatly. I need to concatenate the two as they are be= > > ing displayed as one column in a drop down combobox. > > Use a fixed-width font. > > > Is what I'm trying to do possible??? > > Not with a variable-width font that you haven't even told us the exact > character widths of ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Syntax for cmd to EXEC...how many quotes?
David, I tend to use \ to escape things like ' - I find it makes it somewhat easier to debug. What about: sql_string :=\' INSERT INTO temp_table ( view_name, row_count ) SELECT \' || r_rec.viewname || \', count(*) FROM \' || r_rec.viewname || \' ; \'; HTH George - Original Message - From: "David B" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, April 20, 2004 6:24 PM Subject: [SQL] Syntax for cmd to EXEC...how many quotes? > Folks, > > This is driving me crazy...I'm sure it's possible but that I am getting the > #quotes wrong in some way... > I keep getting unterminated string errors...now matter how many quotes I > use. > > I have a FN that I want to loop through all views and populate a table with > a count(*) from each views. > > To do it I'm doing a LOOP around all views...something like: > > FOR r_rec IN SELECT viewname from pg_views > LOOP > > sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT > ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname || ' ; ' ; > > EXEC sql_string ; > > END LOOP ; > > END ; > > > Building that sql_string is the problem. > Any thoughts folks? > > -D > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004 > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Returning A Varchar From A Function
Hi Richard,
What happens if you just do:
where trim(status) = trim($1)
Regards,
George
- Original Message -
From: "Richard Hurst" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 12, 2004 4:04 AM
Subject: [SQL] Returning A Varchar From A Function
> Hi
>
> this has been puzzling me for a few days now
>
> I have created a function that I want to use in a database to select a
> value from a table based on the value passed in.
> The table is fairly simple
> CREATE TABLE public.feeder_next_status
> (
> status varchar NOT NULL,
> previous_status varchar,
> next_status varchar
> ) WITH OIDS;
>
> The function is defined as
>
> -- Function: public.spgetnextstatus(varchar)
>
> -- DROP FUNCTION public.spgetnextstatus(varchar);
>
> CREATE OR REPLACE FUNCTION public.spgetnextstatus(varchar)
> RETURNS varchar AS
> '
> select cast(next_status as varchar)
> from feeder_next_status
> where trim(status) = trim(\'$1\')
> order by next_status;'
> LANGUAGE 'sql' STABLE;
>
>
> However when i run the query
> select spgetnextstatus('NEW')
> in pgadmin
> the dataoutput shows two columns
> the row column shows a row number of '1' and the column header
> spgetnextstatus(varchar) shows blank
>
> I have tested the equivalent sql in the pgadmin query and it works
> fine.
>
> Hoping someone can point me inthe right direction
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] PL/PgSQL - returning multiple columns ...
Hi Marc,
One option is to create a simple data type and return the rowtype of the
datatype
eg CREATE TYPE tserverload AS ("server_name" text, "load_avg" int4);
CREATE FUNCTION getserverload()
RETURNS tserverload
AS
'DECLARE
r tserverload%rowtype;
etc.
You would then return r, comprised of r.server_name and r.load_avg.
George
- Original Message -
From: "Marc G. Fournier" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, February 02, 2005 3:10 PM
Subject: [SQL] PL/PgSQL - returning multiple columns ...
I have a function that I want to return 'server_name, avg(load_avg)' ...
if I wanted to return matching rows in a table, I can do a 'setof
', with a for loop inside ... but what do I set the 'RETURNS' to if
I want to return the results of query that returns only two fields of a
table, or, in the case of the above, one column and oen 'group by' column?
thanks ...
Marc G. Fournier Hub.Org Networking Services
(http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ:
7615664
---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Working with XML.
Title: Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. Microsoft has a very good reference on xpath expressions: http://msdn.microsoft.com/library/default.asp?url="">. Regards, George - Original Message - From: Theo Galanakis To: [email protected] Sent: Sunday, February 13, 2005 11:48 PM Subject: [SQL] Working with XML. Hi Folks, Is there a way to pass in an xml string into a stored proc and thenplace this into a temp table? I use to be able to do this in sql server, it was quite handy as I could call one stored proc to update multiple records, here is a sample in sql server: select CoverTypeID, ItemSQ, SituationID, ItemDescription, CoverAmount From OpenXML ( @XmlHandle, '/cover/covertype/item',1 ) With ( CoverTypeID int '../@id', ItemSQ int '@id', SituationID int '@situationID', ItemDescription varchar(100) '@description', CoverAmount money '@amount' ) I have managed to get get pgxml_xpath working, however Im not sure how to access specific rows in an xml document. E.g below there are two records, how do I access the second record, the following returns both ,'//query/row/cola values being (284122,525887): select pgxml_xpath( '284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65525887493253{ts ''2005-02-14 16:13:18''}6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3' ,'//query/row/cola/text()','','') __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] Working with XML.
Title: Message
Hi Theo,
I'm not sure if it can be done with the xml contrib
module. You may want to install and work with the xml2 contrib module,
which is more recent, has more extensive capabilities, and is easier to
work with.
It will give you the result you want:
jan28-05=# select xpath_string(jan28-05(#
'284122789648<colc>{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts
''2005-02-14
16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts
''2005-02-14
16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(#
,'//query/@rows') as rows;
rows-- 100(1 row)
Another example:
jan28-05=# select xpath_string(jan28-05(# '284122789648<colc>{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts
''2005-02-14
16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts
''2005-02-14
16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(#
,'//row[cola=284122]/colb') as colb;
colb 789648(1 row)
HTH,
George
- Original Message -
From:
Theo Galanakis
To: 'George Weaver'
Cc: '[email protected]'
Sent: Monday, February 14, 2005 9:46
PM
Subject: RE: [SQL] Working with
XML.
Thanks George.
How do you get an attributes value the following returns
the attribute tag. i.e. rows="100", all i want is the 100.
select pgxml_xpath('284122789648{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts
''2005-02-14
16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts
''2005-02-14
16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','')
Theo
-Original Message-From: George Weaver
[mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39
AMTo: Theo Galanakis; [email protected]:
Re: [SQL] Working with XML.
Hi Theo,
I am not aware of any means of passing xml to
stored procedures, apart from writing your own function to parse the
xml.
In regard to your second question - how to
access the second record - try using a more explicit xpath query
incorporating a "where" component. For example, if you wanted to
access the second row based upon the value of cola, you could use
'//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the
position of the record if you know its position:
'//query/row[2]/text()'.
Microsoft has a very good reference on xpath
expressions: http://msdn.microsoft.com/library/default.asp?url="">.
Regards,
George
- Original Message -
From:
Theo Galanakis
To: [email protected]
Sent: Sunday, February 13, 2005 11:48
PM
Subject: [SQL] Working with
XML.
Hi Folks,
Is there a way to pass in an xml string into a
stored proc and thenplace this into a temp table?
I use to be able to do this in sql server, it
was quite handy as I could call one stored proc to update multiple
records, here is a sample in sql server:
select CoverTypeID, ItemSQ, SituationID,
ItemDescription, CoverAmount From
OpenXML ( @XmlHandle, '/cover/covertype/item',1 )
With ( CoverTypeID int '../@id',
ItemSQ int '@id',
SituationID int
'@situationID',
ItemDescription varchar(100) '@description',
CoverAmount money '@amount' )
I have managed to get get pgxml_xpath working,
however Im not sure how to access specific rows in an xml document. E.g
below there are two records, how do I access the second record, the
following returns both ,'//query/row/cola values being
(284122,525887):
select pgxml_xpath( '284122789648{ts
''2005-02-14
Re: [SQL] Working with XML.
Title: Message Hi Theo, You can find the source code for xml2 it in the 8 source tree http://www.postgresql.org/download/. If you're working with an earlier version of PostgreSQL than 8, you may have to make some modifications to the contrib code to get it to compile and link properly - I should be able to help you with that. I don't think you can use it on any version earlier than 7.2. Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Sent: Tuesday, February 15, 2005 4:30 PM Subject: RE: [SQL] Working with XML. Hi George, Thanks for your help once again. However I cant seem to find XML2, the contrib package for RedHat ES3 I downloaded only has xml. Where can I find the compiled or source code. The only link I have is http://developer.postgresql.org/docs/pgsql/contrib/ , it there ftp access or cvs access to contrib? Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 16 February 2005 12:37 AMTo: Theo GalanakisCc: [email protected]: Re: [SQL] Working with XML. Hi Theo, I'm not sure if it can be done with the xml contrib module. You may want to install and work with the xml2 contrib module, which is more recent, has more extensive capabilities, and is easier to work with. It will give you the result you want: jan28-05=# select xpath_string(jan28-05(# '284122789648<colc>{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//query/@rows') as rows; rows-- 100(1 row) Another example: jan28-05=# select xpath_string(jan28-05(# '284122789648<colc>{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//row[cola=284122]/colb') as colb; colb 789648(1 row) HTH, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: '[email protected]' Sent: Monday, February 14, 2005 9:46 PM Subject: RE: [SQL] Working with XML. Thanks George. How do you get an attributes value the following returns the attribute tag. i.e. rows="100", all i want is the 100. select pgxml_xpath('284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','') Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39 AMTo: Theo Galanakis; [email protected]: Re: [SQL] Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. Microsoft has a very good reference on xp
Re: [SQL] Working with XML.
Title: Message
Hi Theo,There have been 2 major changes
between the 7 and 8 versions that affect the coding in xml2. You need to
edit the source code in order for it to compile properly on 7.First,
work_mem has to be changed to SortMem (line 666).
I.e. tupstore = tuplestore_begin_heap(true, false,
work_mem);should be changed to:
tupstore =
tuplestore_begin_heap(true, SortMem);
Second, the error reporting framework has changed.
To fix this, you need to change all the ereport coding to use
elog. I.e.
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("xpath_table must be called as a table function")));should be
changed to:
elog(ERROR,"xpath_table must be called as a table
function");
I have attached an edited copy of xpath.c with these changes, if you would
like to work with it.
Regards,
George- Original Message - From: Theo Galanakis
To: '[email protected]' Sent: Sunday, February 20, 2005 11:28
PMSubject: FW: [SQL] Working with XML.Hi,
I have copied all the files manually from
http://developer.postgresql.org/docs/pgsql/contrib/ for the xml2 contribution.
However I have the following issue when I attempt to compile with
gmake:gcc -I/usr/include/libxml2 -fpic -I. -I../../src/include
-D_GNU_SOURCE -c -o xpath.o xpath.cxpath.c: In function
`xpath_table':xpath.c:689: `work_mem' undeclared (first use in this
function)xpath.c:689: (Each undeclared identifier is reported only
oncexpath.c:689: for each function it appears in.)gmake: *** [xpath.o]
Error 1I have installed :
libxml2-devel-2.5.10-1.rpmWhat am I doing wrong, or can someone point me
to the direction of a binary for XML2 on RedHat ES3, Postgres
7.4.5.Cheers,
Theo
/* Parser interface for DOM-based parser (libxml) rather than
stream-based SAX-type parser */
/*#include "errcodes.h"*/
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "lib/stringinfo.h"
/* libxml includes */
#include
#include
#include
#include
#include
/* declarations */
static void *pgxml_palloc(size_t size);
static void *pgxml_repalloc(void *ptr, size_t size);
static void pgxml_pfree(void *ptr);
static char *pgxml_pstrdup(const char *string);
static void pgxml_errorHandler(void *ctxt, const char *msg,...);
voidelog_error(int level, char *explain, int force);
voidpgxml_parser_init(void);
static xmlChar *pgxmlNodeSetToText(xmlNodeSetPtr nodeset,
xmlChar * toptagname, xmlChar * septagname,
xmlChar * plainsep);
text *pgxml_result_to_text(xmlXPathObjectPtr res, xmlChar * toptag,
xmlChar * septag, xmlChar * plainsep);
xmlChar*pgxml_texttoxmlchar(text *textstring);
static xmlXPathObjectPtr pgxml_xpath(text *document, xmlChar * xpath);
Datum xml_valid(PG_FUNCTION_ARGS);
Datum xpath_nodeset(PG_FUNCTION_ARGS);
Datum xpath_string(PG_FUNCTION_ARGS);
Datum xpath_number(PG_FUNCTION_ARGS);
Datum xpath_bool(PG_FUNCTION_ARGS);
Datum xpath_list(PG_FUNCTION_ARGS);
Datum xpath_table(PG_FUNCTION_ARGS);
/* Global variables */
char *errbuf; /* per line error buffer */
char *pgxml_errorMsg = NULL; /* overall error message */
/* Convenience macros */
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin,
CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(textp)))
#define ERRBUF_SIZE 200
/* memory handling passthrough functions (e.g. palloc, pstrdup are
currently macros, and the others might become so...) */
static void *
pgxml_palloc(size_t size)
{
/* elog(DEBUG1,"Alloc %d in CMC %x",size,CurrentMemoryContext); */
return palloc(size);
}
static void *
pgxml_repalloc(void *ptr, size_t size)
{
/* elog(DEBUG1,"ReAlloc in CMC %x",CurrentMemoryContext);*/
return repalloc(ptr, size);
}
static void
pgxml_pfree(void *ptr)
{
/* elog(DEBUG1,"Free in CMC %x",CurrentMemoryContext); */
return pfree(ptr);
}
static char *
pgxml_pstrdup(const char *string)
{
return pstrdup(string);
}
/* The error handling function. This formats an error message and sets
* a flag - an ereport will be issued prior to return
*/
static void
pgxml_errorHandler(void *ctxt, const char *msg,...)
{
va_list args;
va_start(args, msg);
vsnprintf(errbuf, ERRBUF_SIZE, msg, args);
va_end(args);
/* Now copy the argument across */
if (pgxml_errorMsg == NULL)
pgxml_errorMsg = pstrdup(errbuf);
else
{
int32 xsize = strlen(pgxml_errorMsg);
pgxml_errorMsg = repalloc(pgxml_errorMsg,
(size_t)
Re: [SQL] Working with XML.
Title: Message Hi Theo, I am not able to duplicate the problem you experienced. I tried the query you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database with success. I then copied all the rows and pasted them to the end so that I would have 100 rows, and the query worked as expected. Perhaps the client you're using is causing the problem. Can you run the query in pgAdmin? Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: '[email protected]' Sent: Monday, February 21, 2005 11:54 PM Subject: RE: [SQL] Working with XML. Thankyou George, XML2 compiled ok... next stummbling block.. when I pass a very long XML string to xpath_string() it crashes the postgres server. Client receives a message like: server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request. Below is the function call, a smaller xml string with say 40 records works fine, anything bigger crashes the server. select xpath_string(' 7741872226632005-02-22
Re: [SQL] Working with XML.
Title: Message Hi Theo, Hmm. Well we're getting into territory that's over my head. I've simply been a user of xml2 and do not know much about its inner workings. Just out of curiosity, what is the size of Sort_Mem in your postgresql.conf? Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: '[email protected]' Sent: Tuesday, February 22, 2005 4:13 PM Subject: Re: [SQL] Working with XML. George, I have run this SP in Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2, with all the same results. We are currently using Redhat ES3 and Postgres 7.4.5. Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 23 February 2005 12:46 AMTo: Theo GalanakisCc: [email protected]: Re: [SQL] Working with XML. Hi Theo, I am not able to duplicate the problem you experienced. I tried the query you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database with success. I then copied all the rows and pasted them to the end so that I would have 100 rows, and the query worked as expected. Perhaps the client you're using is causing the problem. Can you run the query in pgAdmin? Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: '[email protected]' Sent: Monday, February 21, 2005 11:54 PM Subject: RE: [SQL] Working with XML. Thankyou George, XML2 compiled ok... next stummbling block.. when I pass a very long XML string to xpath_string() it crashes the postgres server. Client receives a message like: server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request. Below is the function call, a smaller xml string with say 40 records works fine, anything bigger crashes the server. select xpath_string(' 7741872226632005-02-22 __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] Generic Function
- Original Message - From: <[EMAIL PROTECTED]> To: Sent: Monday, March 14, 2005 12:15 PM Subject: [SQL] Generic Function Hi, Can I built a generic function like: CREATE FUNCTION f (text) RETURNS TEXT as $$ return 'select * from $1'; $$ I know its impossible as writed. Also I have looked for EXECUTE procedure but it not run the correct function. If you show us what you've tried and the results you received we may be able to help more. Some points: 1. To create a string to run with EXECUTE you would need to concatenate the above phrase: (Assuming "query" has been declared as text): query := 'Select * from ' || $1; 2. Since you're likely returning multiple rows, you need to write your function as a set-returning function. See: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING and Executing Dynamic Commands in http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-PERFORM Is there a way to construct this clause? Using plpgsql/pltcl/anything ??? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query performance problem
Only specifying a default value does not prevent a NULL from being entered
either through accident or ignorance:
jan28-05=# create table test (foo text, foo1 int4 default(0));
CREATE TABLE
jan28-05=# insert into test values('a',1);
INSERT 98685 1
jan28-05=# insert into test values('b',4);
INSERT 98686 1
jan28-05=# insert into test values('c',NULL);
INSERT 98687 1
jan28-05=# insert into test values('d');
INSERT 98688 1
jan28-05=# select * from test;
foo | foo1
-+--
a |1
b |4
c |
d |0
(4 rows)
George
- Original Message -
From: "Kenneth Gonsalves" <[EMAIL PROTECTED]>
To: "Richard Huxton"
Cc: <[EMAIL PROTECTED]>;
Sent: Thursday, March 17, 2005 11:19 PM
Subject: Re: [SQL] Query performance problem
On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
Not necessarily. NOT NULL here helps to ensure you can add values
together without the risk of a null result. There are plenty of
"amount" columns that should be not-null (total spent, total
ordered etc).
that makes sense - but is it necessary to have a not null constraint
when there is a default value?
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.sourceforge.net
àà à!
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] SYNTAX ERROR ON FOR... LOOP
Hi Rodrigo, - Original Message - From: "Rodrigo Carvalhaes" <[EMAIL PROTECTED]> To: Sent: Wednesday, April 27, 2005 12:39 PM Subject: [SQL] SYNTAX ERROR ON FOR... LOOP Hi Guys, I am having a "simple syntax problem" but very strange... I am trying to make an IF / ELSE / END IF inside of a FOR ... LOOP but I am getting syntax error on this contol structure... If I comment the IF / ELSE / ENDIF the fuction works ... snip vstrupdate := $$UPDATE table1 SET code = $$ || new_code || $$ WHERE old_code = $$ || recs.old_code || $$ AND is_customer $$; IF records.is_customer IS FALSE THEN vstrupdate := vstrupdate || $$ IS true ;$$; I believe you need an ";" after true and false to complete the string as an SQL statement ELSE vstrupdate := vstrupdate || $$ IS false ;$$; ENDIF; EXECUTE vstrupdate; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CREATE VIEW form stored in database?
- Original Message - From: "Mario Splivalo" <[EMAIL PROTECTED]> Yes, I'm tied to the pgadmin3 for the moment, so there's nothing I could do. It's a pain to develop a database such way. Mario, If you keep your definition in a script file, you can copy the script and paste it into pgAdmin's Execute Arbitrary SQL Queries window, and then execute the script from there. Regards, George ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] CREATE VIEW form stored in database?
- Original Message - From: "Mario Splivalo" <[EMAIL PROTECTED]> If you keep your definition in a script file, you can copy the script and paste it into pgAdmin's Execute Arbitrary SQL Queries window, and then execute the script from there. It's still a pain. If I have two dozen views, it takes too much time :) You also have the option of loading script files in the Execute Arbitray SQL Queries window (File > Open, etc.). Thus you could put all your views into one script file, load the file, and then execute the query. Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problems Testing User-Defined Function
- Original Message -
From: "Rommel the iCeMAn" <[EMAIL PROTECTED]>
I am trying to pass the following values to the function but I have been
so
far unsuccessful.
What error message are you receiving?
SELECT sp_insert_manifest('me', [EMAIL PROTECTED]', '[EMAIL PROTECTED]', 'test.txt',
NULL,
'2006/06/09')
Is this a direct paste? If so, you are missing the apostrophe before me@
you.com.
Regards,
George
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: Fwd: [SQL] Start up question about triggers
Hi Fotis, If you end up having to create a solution for each of the 80 tables, you may want to check out the following (may also give you addtional ideas for what you're trying to achieve): http://www.varlena.com/GeneralBits/104.php (Logging Audit Changes with Composite Typed Columns). Regards, George - Original Message - From: "Forums @ Existanze" <[EMAIL PROTECTED]> To: Sent: Friday, June 23, 2006 2:15 AM Subject: Re: Fwd: [SQL] Start up question about triggers Hello again aaron, Im really interested in the part where you say "generic trigger" can you give me some tips? As to how I will go about that? I had already read the links that Richard gave, I new I could get the values like that. So right now I will have to create a trigger for each of my tables to create the necessary queries, or I could do it "generically" :-) Best Regards, Fotis -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Broersma Jr Sent: 23 June 2006 08:10 To: Aaron Bono; [email protected] Subject: Re: Fwd: [SQL] Start up question about triggers > I did some research and can't even find a way to get meta data in a trigger. > > In a trigger, is there a way to inspect OLD and NEW to see what > columns are there and see what has changed? If so, you may not be > able to grab the actual query but you could create a generic trigger > that reconstructs a possible update/insert/delete for any table in your database. > > Does anyone know of a good place to go get information about using > meta data in a stored procedure or trigger? yes. See the section "User Comments" at the very bottom of Chapter 33 after "Writing Trigger Functions in C". It is odd that a PL_PGSQL example is given at the end a chapter for triggers written in C. http://www.postgresql.org/docs/8.1/interactive/trigger-example.html Also see the entire chapter 36.10 "Trigger Procedures" http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html Hope this is what you are looking for. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: 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] Error when trying to use a FOR loop
- Original Message From: "Kevin Nikiforuk" xp512-0715-0716=# FOR LV in 1..10 LOOP It appears that you are trying to use the For .. Loop structure from the psql command line. This structure is not plain SQL - its meant to be used within PL/pgSQL. Have you tried incorporating it into a PL/pgSQL function? See http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Regards, George ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Triggers using PL/pgSQL
- Original Message - From: Aaron Bono To: John DeSoi I don't think so but there was some discussion a week or two ago about mixing variables and using execute. I am curious, does anyone >know what the "best" approach is? Also, I did not address deletions. If you still need to delete from the table, you will need to get rid of the foreign key on the history table. >You will also need to decide how the history table will reflect the recording of those deletions. You may want to refer to: http://www.varlena.com/GeneralBits/104.php (Logging Audit Changes with Composite Typed Columns). It provides a very concise way of tracking all changes, possible qualifying as a "best approach"? Regards, George ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ERROR: column "crc" does not exist
Original Message From: <[EMAIL PROTECTED]> I have a table with many fields and the latest field is: "CRC" character varying(255), ^^ Select * from base.points where crc = 'e19e26330a0db2f2435106b16623fa82' What happens when you enter: Select * from base.points where "CRC" = 'e19e26330a0db2f2435106b16623fa82'; Regards, George ---(end of broadcast)--- TIP 6: explain analyze is your friend
