[SQL] missing... at end of sql expression
I am trying to learn pspgsql but I am not having a lot of luck. I have written a function but I keep getting the error missing... at end of sql _expression_. Can anyone help! CREATE FUNCTION exp101 () RETURNS text AS ' DECLARE -- Declare a variable to hold book titles and set its default -- value to a new line. text_output TEXT :=''\n''; -- Declare a variable to hold rows from the -- books table. --row_data enrollment%ROWTYPE; --row_dataa student%ROWTYPE; BEGIN -- Iterate through the results of a query. FOR row_data IN select * from enrollment, student where enrollment.stdid = cast(student.stdid as char(4)) Loop FOR row_dataa IN select student.stdid , student.lname , student.fname ,student.mi, student.phone , student.major, advisor.lname, advisor.fname, advisor.mi from student, enrollment, advisor where enrollment.stdid = cast(student.stdid as char(4)) and student.advid = advisor.advid and enrollment.crs = row_data.crs order by student.lname, student.fname, student.mi Loop -- Insert the title of a matching book into the text_output variable. text_output := text_output || row_data.crs ||''\n''; END LOOP; end loop; -- Return the list of books. RETURN text_output; END; ' LANGUAGE 'plpgsql';
[SQL] How to make lo_import and lo_export to use file on client local machine?
I try to use lo_import and lo_export for manage large object on PostgreSQL. I found that all files must be process on server with these commands. Could I make lo_import and lo_export to use file on client local machine? And How? Or Is it has other ways to manage large object that process is between client local machine and PostgreSQL database? ---(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] missing... at end of sql expression
[EMAIL PROTECTED] writes: > I am trying to learn pspgsql but I am not having a lot of luck. > I have written a function but I keep getting the error missing... at end of > sql expression. Can anyone help! What it's really unhappy about is that you failed to declare the FOR variable; this is causing it to assume that the FOR is the integer variant (FOR i IN low .. high). The error message is pretty confusing, I agree. I think 8.0 will do better. 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])
Re: [SQL] missing... at end of sql expression
On Thu, Nov 18, 2004 at 12:28:03PM -0700, [EMAIL PROTECTED] wrote: > --row_data enrollment%ROWTYPE; > --row_dataa student%ROWTYPE; Your loop variables are commented out. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[SQL] SQL Question
Hi, If anybody can offer better SQL query please. We have table create table group_facility ( group_id integer not null, facility_id integer not null ) It stores facilities membership in group. For example: "North Region" - facilityA, facilityB I need to extract groups from this table which contain facilityN AND facilityZ and may be others but these two(both) has to be a group member. Query: SELECT DISTINCT group_id FROM facility_group s1 WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 390) AND EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 999) works but what if I need to find groups where membership is (facilityN1, facilityN100)?? Thank you, Igor K ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Question
Igor Kryltsov wrote: We have table create table group_facility ( group_id integer not null, facility_id integer not null ) It stores facilities membership in group. For example: "North Region" - facilityA, facilityB I need to extract groups from this table which contain facilityN AND facilityZ and may be others but these two(both) has to be a group member. Query: SELECT DISTINCT group_id FROM facility_group s1 WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 390) AND EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND facility_id = 999) works but what if I need to find groups where membership is (facilityN1, facilityN100)?? Okay: suppose you have table my_facilities(facility_id integer) --- your facilityN1...facilityN100 SELECT group_id FROMfacility_group s1 JOINmy_facilities s2 USING(facility_id) GROUP BY group_id HAVING COUNT(*) = (SELECT COUNT(*) FROM my_facilities) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] session_id
Riccardo G. Facchini wrote: > No, I can't provide it because your'e right. currval() is NOT affected > by other sessions. > > Thanks anyway for the suggestion, using pg_backend_pid() solved the > uniqueness I needed. I'll keep the nextval/currval for another > opportunity. FYI, we needed a unique-through-time session id for the log_line_prefix so we used the seconds-since-1970-dot-pid. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
