Re: [SQL] [PHP] PEAR Problem
Hi, maybe this problem isn't originated in PEAR, but in pg itself. Postgres folds everything to lowercase except one within "" (doublequotes). So, if you - ie.: in psql: psql=# CREATE TABLE veRYMixedCAse (NetCode integer,...); then actually you will create a table named 'verymixedcase'. In PHP (PEAR actually) you need to refer to this table and its columns in lowercase. You may run psql and execute: \dt-- to figure out what names to be given to your tables and then \d NetworkTab or \d networktab -- and what names of the columns have - Original Message - From: "Gurudutt" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, October 08, 2001 2:37 PM Subject: [PHP] PEAR Problem > Hi, > > It's me again. I have been able to solve most of the porting problems > from mysql to pgsql. But I have got struck in one place. I have a > problem where PEAR's associative array doesn't recognise the mix case > letters. > > eg . I issue a query through PEAR db and get results using fetchRow of > PEAR. Now I have set associative array feature ON. > > suppose the query is > > select NetCode,NetworkName from NetworkTab; > > this would return the result into a variable called $dbRow > > to echo the contents returned by the pgsql, I have to give > $dbRow[NetCode] and $dbRow[NetworkName] > > This used to work perfectly fine with mysql, but as I moved to pgsql, > PEAR started to return nothing > > like if i echo $dbRow[NetCode] it prints nothing > > but in the same echo is I change it echo $dbRow[netcode], it prints > the value of the Network Code. > > How do I handle this situation. The application is fully written with Mix Case > letters for the database fields and returned result set. > > And one more thing "SET AUTOCOMMIT=0" which is to set auto commiting > to "No" in mysql doesn't work in pgsql what is the equivalent command. > > -- > Best regards, > Gurudutt mailto:[EMAIL PROTECTED] > > Life is not fair - get used to it. > Bill Gates > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PHP] PEAR Problem
This is caused by the fact that PostgreSQL is case insensitive. In order for it to actually take the case into account, you need quote your field names and table names. So your query would then be: select "NetCode","NetworkName" from "NetworkTab"; Then you would be able to access the fields with $dbRow[NetworkName]. -Dan : Hi, : : It's me again. I have been able to solve most of the porting problems : from mysql to pgsql. But I have got struck in one place. I have a : problem where PEAR's associative array doesn't recognise the mix case : letters. : : eg . I issue a query through PEAR db and get results using fetchRow of : PEAR. Now I have set associative array feature ON. : : suppose the query is : : select NetCode,NetworkName from NetworkTab; : : this would return the result into a variable called $dbRow : : to echo the contents returned by the pgsql, I have to give : $dbRow[NetCode] and $dbRow[NetworkName] : : This used to work perfectly fine with mysql, but as I moved to pgsql, : PEAR started to return nothing : : like if i echo $dbRow[NetCode] it prints nothing : : but in the same echo is I change it echo $dbRow[netcode], it prints : the value of the Network Code. : : How do I handle this situation. The application is fully written with Mix Case : letters for the database fields and returned result set. : : And one more thing "SET AUTOCOMMIT=0" which is to set auto commiting : to "No" in mysql doesn't work in pgsql what is the equivalent command. : : -- : Best regards, : Gurudutt mailto:[EMAIL PROTECTED] : : Life is not fair - get used to it. : Bill Gates : : : ---(end of broadcast)--- : TIP 5: Have you checked our extensive FAQ? : : http://www.postgresql.org/users-lounge/docs/faq.html : ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] to_char()??
thanks
I have run "select substr('hi there', 3, 5)::varchar(5) as xx;"
but get error message
Error: ERROR: parser: parse error at or near ":"
--
"Lee Harr" <[EMAIL PROTECTED]> ¼¶¼g©ó¶l¥ó·s»D
:9qd0j0$1gc3$[EMAIL PROTECTED]
> >
> > how to
> > select substr('hi there',3,5) as xx -->> xx change char type
> >
>
> How about:
>
> select substr('hi there', 3, 5)::varchar(5) as xx;
>
> or is this not what you mean?
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Indexes
On Sat, 13 Oct 2001 14:17:48 GMT, "Aasmund Midttun Godal" <[EMAIL PROTECTED]> wrote: > On Fri, 12 Oct 2001 19:33:46 -0700 (PDT), Stephan Szabo ><[EMAIL PROTECTED]> wrote: > Well, then another question; will a function be only evaluated once inside a query >with the specific arguments? > > i.e.will > > my_table > > foo|bar > 1 |2 > 1 |3 > 2 |1 > 1 |3 > > > SELECT foo, bar FROM my_table WHERE func(foo, bar); > > will func be evaluated 3 or 4 times ? > I did some research on this issue. and I noticed the following: the func was executed for each row i.e. 4 times. However if I set iscachable it was only executed 3 times. Yet it was executed again next time I called the select (I would have expected it to still be cached) is this intentional? Or would it be a good idea to introduce a new option, iscacheable (permanently cachable) querycachable (cachable within a given query) If there is some info on this issue please let me know! > Aasmund Midttun Godal > > [EMAIL PROTECTED] - http://www.godal.com/ > +47 40 45 20 46 Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(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] Why would this slow the query down so much?
Stuart Grimshaw <[EMAIL PROTECTED]> writes: > SELECT a.category, b.headline, b.added, c.friendlyname > FROM caturljoin as a > INNER JOIN stories as b ON (a.url = b.source) > INNER JOIN urllist as c ON (a.url = d.urn) > WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1; (I assume "d.urn" is a typo for "c.urn"...) The query plan you show looks pretty reasonable if the planner's row count estimates are in the right ballpark. How many caturljoin rows have category = 93? How many stories rows will match each caturljoin row? How many urllist rows ditto? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Restricting access to Large objects
I sent with the wrong email first. sorry. I can't find any docs / mails on this subject. How can I restrict access to large objects. 1.) Based on users. 2.) Rules/triggers? Thanx. Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 -- End Forwarded Message -- Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL reference card
Hello, I'm looking for a reference card for SQL. I've searched quite a lot, but a simple card in PostScript/PDF seems to be hiding for me ;) Anyone?? Thanx in advance, Bob.. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Why would this slow the query down so much?
I have 3 tables that I am trying to join together: -- Table "caturljoin" Attribute | Type | ---+-+ category | integer | url | integer | Index: caturljoin_url caturljoin_cat Table "stories" Attribute | Type | -++ urn | integer| headline| character varying | author | character varying | source | integer| story | text | added | date | description | character varying | displayall | smallint | fullurl | character varying(255) | publish | smallint | error | integer| sourceurl | character varying(255) | sourcename | character varying(100) | rank| smallint | Indices: stories_added, stories_source, stories_unique_story, stories_urn_key Table "urllist" Attribute | Type | --++ urn | integer| url | character varying(255) | friendlyname | character varying(30) | homepage | character varying(255) | method | smallint | script | character varying(20) | params | character varying(500) | collect | smallint | section | smallint | index_script | character varying | regexp | character varying(100) | baseurl | character varying(75) | Index: urllist_urn -- With the following SQL: -- SELECT a.category, b.headline, b.added, c.friendlyname FROM caturljoin as a INNER JOIN stories as b ON (a.url = b.source) INNER JOIN urllist as c ON (a.url = d.urn) WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1; -- The results of explain for the above are: -- psql:scratch.sql:5: NOTICE: QUERY PLAN: Limit (cost=1587.30..1587.30 rows=1 width=44) -> Sort (cost=1587.30..1587.30 rows=1 width=44) -> Merge Join (cost=249.89..1587.29 rows=1 width=44) -> Sort (cost=249.89..249.89 rows=409 width=28) -> Nested Loop (cost=0.00..232.15 rows=409 width=28) -> Index Scan using caturljoin_cat on caturljoin a (cost=0.00..5.09 rows=7 width=8) -> Index Scan using stories_source on stories b (cost=0.00..34.41 rows=29 width=20) -> Index Scan using urllist_urn on urllist c (cost=0.00..1323.69 rows=505 width=16) EXPLAIN -- and as you might be able to guess the query takes an age to complete. If I remove the table urllist from the query, I get a much better response: -- psql:scratch.sql:4: NOTICE: QUERY PLAN: Limit (cost=0.00..207.74 rows=1 width=28) -> Nested Loop (cost=0.00..84945.18 rows=409 width=28) -> Index Scan Backward using stories_added on stories b (cost=0.00..2310.04 rows=16149 width=20) -> Index Scan using caturljoin_url on caturljoin a (cost=0.00..5.10 rows=1 width=8) EXPLAIN -- Currently the tables contain the following rows of data: -- caturljoin: 653 rows urllist: 505 rows stories: 21554 rows -- Can anyone tell me why the inclusion of urllist would slow it down so much, and what can I do to improve the speed of the query? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] count(*) and limit
I have a interface where users are paging through results of a query. In order to do so I need to select count(*) from data where stuff; To find out how many pages of data there are ( needed for the navigation bar) and then I need to select * from data where stuff limit X offset y; Now, postgres has to get the whole result before running the limit. Is there some trick to get how many rows there would be without the limit so I dont need to run the extra count(*) query? It seems like a waste. Orion ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] problem w/plpgsql proc
Hi all - This might be the wrong forum for this, but I don't want to cross-post unless someone tells me I should. Anyways, I'm having a problem trying to compile this plpg proc. I'll post the proc and the errors. I'm coming from a T-SQL background, so hopefully when I get the hang of Pl/PgSQL I'll be able to draft up a doc for porting from T-SQL to Pl/PgSQL. proc: /** *editEmail: all vars required - will renumber the sequence if needed - if the email address is not found, it will add it. returns: -1 - the user already has 8 entries 0 - the record was updated, and the table renumbered 1 - the record was added. **/ --DROP FUNCTION editEmail(integer, smallint, varchar, varchar); CREATE FUNCTION editEmail(integer, smallint, varchar, varchar) RETURNS integer AS ' DECLARE ufk ALIAS FOR $1; seq ALIAS FOR $2; em ALIAS FOR $3; emp ALIAS FOR $4; --for renumbering the records rec RECORD; cnt INTEGER; found SMALLINT := 0; BEGIN -- see if the email address exists, then see if renumbering is needed CREATE TEMP TABLE this_user AS SELECT * FROM tblemailadd WHERE emuserfk = ufk; GET DIAGNOSTICS cnt = ROW_COUNT; --equiv. to @@ROWCOUNT -- see if the user exists in the table, then see if the user already -- has 8 entries. If so - return -1 (error)... max 8 entries allowed :) IF (cnt > 7) THEN IF NOT EXISTS (SELECT emseqnum FROM this_user WHERE emailaddr = em; ) THEN RETURN -1; END IF; END IF; --see if renumbering is needed.. IF (cnt > 1) THEN FOR rec IN SELECT * FROM this_user LOOP; --renumber the sequences UPDATE tblemailadd SET emseqnum = rec.emseqnum + 1 WHERE emuserfk = ufk AND emailaddr = rec.emailaddr; IF (em = rec.emailaddr) THEN found = 1; -- looks like we found the email addr. END IF; END LOOP; -- if the emailaddr was found, then update the record. -- if it wasn't, then insert the new record. IF (found = 1) THEN UPDATE tblemailadd SET emseqnum = seq, emailaddr = em, emprettyname = emp 121 >>> WHERE emuserfk = ufk; RETURN 0; ELSE INSERT tblemailadd (emuserfk, emseqnum, emailaddr, emprettyname) VALUES (ufk, seq, em, emp); RETURN 1; END IF; ELSE IF (cnt > 7) THEN RETURN -1; --alas! the user has too many records to proceed! END IF --make sure that the sequencing order begins intact IF (cnt = 1 AND seq = 1) THEN seq := 2; ELSE IF (cnt = 0 AND seq != 1) THEN seq := 1 END IF; END IF; INSERT tblemailadd (emuserfk, emseqnum, emailaddr, emprettyname) VALUES (ufk, seq, em, emp); RETURN 1; --huzahh! the record has been added! END IF; END; 'LANGUAGE 'plpgsql'; errors: psql:edit_procs.sql:121: ERROR: parser: parse error at or near "t" psql:edit_procs.sql:122: ERROR: parser: parse error at or near "return" psql:edit_procs.sql:125: ERROR: parser: parse error at or near "ELSE" psql:edit_procs.sql:126: ERROR: parser: parse error at or near "return" psql:edit_procs.sql:127: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:131: ERROR: parser: parse error at or near "ELSE" psql:edit_procs.sql:136: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:140: ERROR: parser: parse error at or near "ELSE" psql:edit_procs.sql:141: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:144: ERROR: parser: parse error at or near "tblemailadd" psql:edit_procs.sql:146: ERROR: parser: parse error at or near "return" psql:edit_procs.sql:147: ERROR: parser: parse error at or near "if" psql:edit_procs.sql:148: NOTICE: COMMIT: no transaction in progress COMMIT this is part of a larger script, but the function declaration before this works perfectly, so I assume the problem lies here. sorry for the length... TIA leo ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Restricting access to Large objects
I can't find any docs / mails on this subject. How can I restrict access to large objects. 1.) Based on users. 2.) Rules/triggers? Thanx. Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] GROUPING
On Sat, 13 Oct 2001 03:32:57 + (UTC), <[EMAIL PROTECTED]> wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > I seem to recall reading something recently showing that mysql does something completely different with GROUP from what postgres does. Stephan posted a nice description of how GROUP works here. It might help if we knew what you want the result of your query to be. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] GROUPING
On Sat, 13 Oct 2001, Timothy J Hitchens wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > > Result: > > Attribute telemetry.rpvuid must be GROUPed or used in an aggregate > function > > > Oh then if I include rpvuid I get you must include this field and on it > goes. Normally, GROUP BY is used to group up records to look at an aggregate. For example, if you have this table of your friends: CREATE TABLE friends ( friend TEXT, country_code CHAR(2), income FLOAT, ); I could get a count of how many friends lived in each country by: SELECT country_code, COUNT(*) FROM friends GROUP BY country_code; Or I could get the average amount of money made by friends in each country with: SELECT country_code, avg(income) FROM friends GROUP BY country_code; In other words, when you GROUP BY, you're looking for an aggregate (a function that is applied to a group and returns a single value, such as average, minimum, maximum, count, etc.) Can you be more specific about what you're actually trying to accomplish? ---(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] to_char()??
>
> how to
> select substr('hi there',3,5) as xx -->> xx change char type
>
How about:
select substr('hi there', 3, 5)::varchar(5) as xx;
or is this not what you mean?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Cenceptual help needed - periodic events
I'm goofing around, designing a planned maintenance system. In a couple of weeks I'll be taking on the actual scheduling of tasks. How would you experts out there approach this? I've beeen thinking I can approach this two ways. 1) When a list of tasks is requested, generate new rows and store them. 2) When a list of tasks is requested, look at the task row and calculate the tasks, display them, but do not generate rows for storage. Just calc on the fly all the time. Method #1 would mean less CPU and RAM use but then I'd have to worry about tasks changing or being added to a maintenance schedule and not being reflected in any pre-generated rows. #2 would alleviate that problem, but may be slower overall and perhaps eat large amounts of ram. If anyone has done something similar with periodic events, what did you find worked best? I'm planning on using a base task record to record the frequency and other specifics of a task. This record is the basis for generating the periodic tasks. One row: task_id =1 (serial) task_system = 1 task_equip = 12 task_text = Lubricate fan shafts with lithium grease task_interval = 1 month (using an interval type column) task_startmonth = 2 (offset so that not ALL 6 month interval tasks actuall happen in June) Another row: task_id =25 (serial) task_system = 8 task_equip = 72 task_text = task_interval = 6 month (using an interval type column) task_startmonth=3 Then I'll have to step through the calendar by month and match up all the task rows and print out a single page for each system. This is all a brand new concept to me, so any suggestions are more than welcome. I still have a few weeks work in other areas to keep me busy, but this is the toughy that I'm spending brain time on =) ---(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] SQL reference card
Bob, > I'm looking for a reference card for SQL. I've searched quite a lot, > but a > simple card > in PostScript/PDF seems to be hiding for me ;) It's not hiding from you. There isn't one. It'd be a little hard to reduce all of SQL to a two-sided card. The definition of SELECT alone would take up one side There is a company that does 8.5"x11" reference cards and sells them in tech/academic bookstores. I don't know the name, but you could try your nearest major bookstore, and see if they have SQL as well as MS Office and VBA. Failing that, you can write your own and post it to Techdocs.postgresql.org. That's what open source is all about! -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. 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])
Re: [SQL] problem w/plpgsql proc
Leo, You're gonna feel like a dork when I point it out ... > -- if the emailaddr was found, then update the record. > -- if it wasn't, then insert the new record. ^^^ SINGLE QUOTE HERE!! BAD! You can't have single quotes inside a function, especially in comments. They will be treated as the end of the function definition string. If you write the T-SQL Procedure ---> PostgreSQL function porting guide, I volunteer to edit (certifiable MS SQL DBA). You might wanna wait for 7.2, where Jan says that cursor support in PL/pgSQL functions will be added. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] When will vacuum go away?
I've been watching for this for some time. First it was 7.0, then 7.1. Does anyone have any idea on when the row re-use code will be ready? Currently I'm running into trouble with an OLTP database. It grows like crazy, has only 3,000,000 rows and vacuum takes a good 1/2 hour. Given trouble with Great Bridge is there any info out there on when 7.2 might hit the streets? -Michael _ http://fastmail.ca/ - Fast Free Web Email for Canadians ---(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] problem w/plpgsql proc
leo <[EMAIL PROTECTED]> writes: > CREATE FUNCTION editEmail(integer, smallint, varchar, varchar) RETURNS > integer AS ' > ... > -- if it wasn't, then insert the new record. An undoubled quote mark in a function body is bad news... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Restricting access to Large objects
"Aasmund Midttun Godal" <[EMAIL PROTECTED]> writes: > How can I restrict access to large objects. You can't. This is one of the many deficiencies of large objects. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Cenceptual help needed - periodic events
Pat, > I'm goofing around, designing a planned maintenance system. In a > couple of > weeks I'll be taking on the actual scheduling of tasks. How would you > experts out there approach this? I've beeen thinking I can approach > this two > ways. I'm working on a legal calendar system, and we're storing all events as rows in the database. However, in our case the reasons are overwhelming, since the rules on how new events are generated (aside from those entered by users) are complex enough to fill several pages. Calculating on the fly isn't conceivable. Also, all of our events are, at one level or another, dependant on a user-entered event. Thus we can make all auto-generated events "children" of the parent event and change them when the parent changes. I designed another system that was all auto-scheduling -- a weekly produce delivery system. In this system, we took the expedient of not generating any even more than a week ahead of time, and providing functions that would drop all events and then re-create them, Differently, in a time entry system I designed all of the due dates for timecards were calculated on the fly by a complex view. So it depends on the number and complexity of the rules you will be applying. If the rules are very complex and/or require procedural logic, you probably want to generate and store records. Otherwise, no. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. 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])
Re: [SQL] problem w/plpgsql proc
I may be wrong, but I believe you should not have a semicolon after beginning a loop (in the for clause) and the IF NOT EXISTS clause should be different: you are probably wanting FOUND which is a special variable so you will have to do the select first and then check the FOUND variable afterwards. You are also trying to use FOUND as a normal variable, which I suspect will fail because it is a reserved word. Regards, Aasmund. On Fri, 12 Oct 2001 11:05:45 -0500, leo <[EMAIL PROTECTED]> wrote: > Hi all - > This might be the wrong forum for this, but I don't want to cross-post > unless someone tells me I should. > > Anyways, I'm having a problem trying to compile this plpg proc. I'll post > the proc and the errors. I'm coming from a T-SQL background, so hopefully > when I get the hang of Pl/PgSQL I'll be able to draft up a doc for porting > from T-SQL to Pl/PgSQL. > > proc: > /** > *editEmail: all vars required > - will renumber the sequence if needed > - if the email address is not found, it will add it. > returns: > -1 - the user already has 8 entries > 0 - the record was updated, and the table renumbered > 1 - the record was added. > **/ > > --DROP FUNCTION editEmail(integer, smallint, varchar, varchar); > > CREATE FUNCTION editEmail(integer, smallint, varchar, varchar) RETURNS > integer AS ' > DECLARE > ufk ALIAS FOR $1; > seq ALIAS FOR $2; > em ALIAS FOR $3; > emp ALIAS FOR $4; > > --for renumbering the records > rec RECORD; > cnt INTEGER; > found SMALLINT := 0; > BEGIN > -- see if the email address exists, then see if renumbering is needed > CREATE TEMP TABLE this_user AS > SELECT * FROM tblemailadd WHERE emuserfk = ufk; > > GET DIAGNOSTICS cnt = ROW_COUNT; --equiv. to @@ROWCOUNT > > -- see if the user exists in the table, then see if the user already > -- has 8 entries. If so - return -1 (error)... max 8 entries allowed :) > IF (cnt > 7) THEN > IF NOT EXISTS (SELECT emseqnum FROM this_user WHERE emailaddr = em; ) THEN > RETURN -1; > END IF; > END IF; > > --see if renumbering is needed.. > IF (cnt > 1) THEN > FOR rec IN SELECT * FROM this_user LOOP; > --renumber the sequences > UPDATE tblemailadd SET > emseqnum = rec.emseqnum + 1 > WHERE emuserfk = ufk AND emailaddr = rec.emailaddr; > > IF (em = rec.emailaddr) THEN > found = 1; -- looks like we found the email >addr. > END IF; > END LOOP; > > -- if the emailaddr was found, then update the record. > -- if it wasn't, then insert the new record. > IF (found = 1) THEN > UPDATE tblemailadd SET > emseqnum = seq, emailaddr = em, emprettyname = emp > 121 >>> WHERE emuserfk = ufk; > RETURN 0; > ELSE > INSERT tblemailadd (emuserfk, emseqnum, emailaddr, >emprettyname) > VALUES (ufk, seq, em, emp); > RETURN 1; > END IF; > > ELSE > IF (cnt > 7) THEN > RETURN -1; --alas! the user has too many records to proceed! > END IF > > --make sure that the sequencing order begins intact > IF (cnt = 1 AND seq = 1) THEN > seq := 2; > ELSE > IF (cnt = 0 AND seq != 1) THEN > seq := 1 > END IF; > END IF; > > INSERT tblemailadd (emuserfk, emseqnum, emailaddr, emprettyname) > VALUES (ufk, seq, em, emp); > > RETURN 1; --huzahh! the record has been added! > END IF; > END; > 'LANGUAGE 'plpgsql'; > > errors: > psql:edit_procs.sql:121: ERROR: parser: parse error at or near "t" > psql:edit_procs.sql:122: ERROR: parser: parse error at or near "return" > psql:edit_procs.sql:125: ERROR: parser: parse error at or near "ELSE" > psql:edit_procs.sql:126: ERROR: parser: parse error at or near "return" > psql:edit_procs.sql:127: ERROR: parser: parse error at or near "if" > psql:edit_procs.sql:131: ERROR: parser: parse error at or near "ELSE" > psql:edit_procs.sql:136: ERROR: parser: parse error at or near "if" > psql:edit_procs.sql:140: ERROR: parser: parse error at or near "ELSE" > psql:edit_procs.sql:141: ERROR: parser: parse error at or near "if" > psql:edit_procs.sql:144: ERROR: parser: parse error at or near > "tblemailadd" >
[SQL] EXECUTE ... INTO?
Folks, Can anybody tell me the syntax for sending the result of an EXECUTE to a variable within a PL/pgSQL function again? Jan Wieck posted it to the list this summer, but the "searchable list archives" are bogging down. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
