[GENERAL] PLPGSQL: Using SELECT INTO and EXECUTE
Can EXECUTE handle a SELECT INTO statement within a plpgsql function. Here is what I am trying to do. The standard SELECT INTO statement: SELECT INTO session_logins_id s.session_logins_id FROM session_logins s WHERE s.username = session_login_in; The problem with using a standard SELECT INTO statement within a plpgsql function is that I need to dynamically assign the table name in the FROM clause. Since plpgsql cannot parse a variable within a standard SQL statement I issue the EXECUTE command using a concatenated SQL statement inside a variable. Such that: DECLARE session_login_inALIAS FOR $x; session_logins_idINTEGER; BEGIN sql_command := ''SELECT INTO session_logins_id s.session_logins_id FROM '' || table_name || '' s WHERE s.username = '''''' || session_login_in || '''''';''; EXECUTE sql_command; This is but one variation I have tried to pass to the EXECUTE command.. but, in all instances it errors out. This particular example above errors out with the following: ERROR: parser: parse error at or near "INTO". A second variation would be to isolate the plpgsql variable session_logins_id outside the command: sql_command := ''SELECT INTO '' || session_logins_id || '' s.session_logins_id FROM '' || table_name || '' s WHERE s.username = '''''' || session_login_in || '''''';''; But, this second variation returns a null string inside the sql_command variable and obviously errors out with the EXECUTE command not being able to execute a null query. Am I not structuring the command correctly to be passed to the EXECUTE statement?? Or, is it not possible to use a SELECT INTO statement using the EXECUTE command? The only other workaround I can think of is calling a c function from a stored prcedure, but then I am concerned with degradation in performance since this particular function would be handling a large amount of requests a second. Additionally, I would like to maintain continuity in the code and do not want to introduce another language into the scheme. Any suggestions would be greatly appreciated. Thanks Regards, Michael Dunn ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Obtaining nTuples from a cursor
I would like to know if there is a way to return nTuples for a cursor without having to FETCH ALL from the cursor. I am sorting over 500k records and growing.. and return 1k at time from the cursor. But, It I also need to obtain the total number of records in the cursor without having to either do a SELECT count(*) of the same query or returning ALL from the cursor. It is entirely innefficient... Regards, Michael
Re: [GENERAL] Order of Rules
I wrote a plpgsql function/stored procedure to get around this problem. I am not sure specifically what information you need about the row after INSERT, but you could write a function that performs the INSERTs and summing. After the initial INSERT you can use a SELECT INTO statement to pass whatever values you are summing and any other values you might require into function variables. You would then perform your secondary INSERT using the summed values from the variables. It may not be the most efficient way to achieve this... but it works. EXAMPLE: -- You could specify NULL or BOOL if no -- value is needed for a return value CREATE FUNCTION some_function (INTEGER, TEXT) RETURNS INTEGER AS ' DECLARE arg1ALIAS FOR $1; arg2ALIAS FOR $2; a1 INTEGER; a2 INTEGER; sum INTEGER; INSERT INTO a VALUES (arg1, arg2); SELECT INTO a1 a.value_1, a2 a.value2 FROM a WHERE comparison1 = arg1 AND comparison2 = arg2; sum := a1 + a2; INSERT INTO b VALUES (sum); return sum; -- or bool, NULL if no return vaue is needed END;' LANGUAGE 'plpgsql'; Obviously this is a very simplified version of what you are wanting to do, but it should give you a good idea where to take it. More than likely you will also want to include a test ISNULL for the variables being passed values from the SELECT INTO and raise an exception if NULL. Also, it would be much more effecient to know the PK for the table you are using for the SELECT INTO rather than selecting on the inserted values. Another alternative would be using the libpq API and write the function in C. You can then call the C prog. from inside Postgres. There is sufficient documentation on this... providing you have a good foundation writing C code. This is the alternative I eventually chose. However, my function is process intensive sorting and evaluating on average over 500K records and growing. If yours is not... the 'plpgsql' function will more than likely suffice. Good luck... and I hope this works out for you. And remember, have FUN!! Regards, Michael >> I read at the docs that rules are executed before the query that >> generated them. Is there any way to change this? >> >> Here's my problem: I have two tables, A and B, and after an INSERT on >> table A, I want to update an specific field on table B, with the result >> of a sum in A. To do this I needed some information about the row just >> inserted, so I used rules. As my sum is running before the insertion, >> its result is wrong. >> >> Any ideas? >> >> Tks >> >> -- >> Nelio Alves Pereira Filho >> IFX Networks - www.ifx.com.br >> +55 11 3365-5863 >> [EMAIL PROTECTED] >>
