[SQL] Problem with inserting data produced by the function (when the function is performed).
The function calculates the data line by line, and inserts into the
table, at the end of the function I need to send data from a table to
another database via dblink. I noticed that the problem is to perform
this operation in one function (or the main function of the two
subfunction). My guess is that unless the function has finished
running postgres no data dumps. Data is available in the table after
the function finished, and I can send them without a problem.
The operation must be done in one function or the main function and
two of subfunction (first subfunction loads the data into a table,
second subfunction sends data to another database via dblik).
How to force a permanent record of data in a table when executing the
function?
Sample code:
--
CREATE OR REPLACE FUNCTION filter_reports_yesterday()
RETURNS void AS
$BODY$declare
target_table_name varchar;
res record;
cr cursor for
select distinct substr(tablename, 0, position('_row' in tablename))
as adr_sym from pg_tables
where tablename like '%_row' and schemaname = 'arch_'||
to_char((current_date-1),'_MM');
al_cr refcursor;
begin
for res in cr
loop
--first subfunction generating data in table
execute 'select
packet_filter_yesterday('''||(current_date-1)||''',
'''||res.adr_sym||''')';
target_table_name := 'rap_' ||
to_char((current_date-1),'_MM')
|| '.' || res.adr_sym || '_fil';
--second function inserting data from table to another database
via
dblink
execute 'select insert_FS3('''||target_table_name||''')';
end loop;
end$BODY$
LANGUAGE plpgsql
--
When doing the first function (for a single table, no loops) and after
the second run separately the data is sent. When both feature walk in
a loop in main function is the result of lack of data.
thanks in advance
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Can I read the data without commit
Hi, I know this is a newbie question but I have never had the need to do the following. I start a transaction. Begin Then I insert a lot of data - let's say two hundred rows. Now I need to read the same data (so the user can review). If the user thinks all is right then commit. Can I read the data I just inserted without committing? Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can I read the data without commit
On Mar 23, 2012, at 5:33 PM, John Fabiani wrote: > I start a transaction. > Begin > > Then I insert a lot of data - let's say two hundred rows. > > Now I need to read the same data (so the user can review). > > If the user thinks all is right then > commit. > > Can I read the data I just inserted without committing? Yes, as long as you run your SELECT within the transaction that you started. Jonathan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can I read the data without commit
If possible have the review done before starting the transaction. No sense in holding on to that stuff too long. Potential concurrency issues etc. On 03/23/2012 03:40 PM, Jonathan S. Katz wrote: On Mar 23, 2012, at 5:33 PM, John Fabiani wrote: I start a transaction. Begin Then I insert a lot of data - let's say two hundred rows. Now I need to read the same data (so the user can review). If the user thinks all is right then commit. Can I read the data I just inserted without committing? Yes, as long as you run your SELECT within the transaction that you started. Jonathan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can I read the data without commit
Yes I understand - but I want to know is it possible? Can I read the data I just inserted without a commit. Johnf On Friday, March 23, 2012 03:46:10 PM Rob Sargent wrote: > If possible have the review done before starting the transaction. No > sense in holding on to that stuff too long. Potential concurrency issues > etc. > > On 03/23/2012 03:40 PM, Jonathan S. Katz wrote: > > On Mar 23, 2012, at 5:33 PM, John Fabiani wrote: > >> I start a transaction. > >> Begin > >> > >> Then I insert a lot of data - let's say two hundred rows. > >> > >> Now I need to read the same data (so the user can review). > >> > >> If the user thinks all is right then > >> commit. > >> > >> Can I read the data I just inserted without committing? > > > > Yes, as long as you run your SELECT within the transaction that you > > started. > > > > Jonathan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can I read the data without commit
And I believe Jonathon confirmed that you could, with the caveat that you must select from within the transaction. I don't see that you've laid that out your connection stategy so ymmv. On 03/23/2012 03:49 PM, John Fabiani wrote: Yes I understand - but I want to know is it possible? Can I read the data I just inserted without a commit. Johnf On Friday, March 23, 2012 03:46:10 PM Rob Sargent wrote: If possible have the review done before starting the transaction. No sense in holding on to that stuff too long. Potential concurrency issues etc. On 03/23/2012 03:40 PM, Jonathan S. Katz wrote: On Mar 23, 2012, at 5:33 PM, John Fabiani wrote: I start a transaction. Begin Then I insert a lot of data - let's say two hundred rows. Now I need to read the same data (so the user can review). If the user thinks all is right then commit. Can I read the data I just inserted without committing? Yes, as long as you run your SELECT within the transaction that you started. Jonathan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can I read the data without commit
On Friday, March 23, 2012 04:00:56 PM Rob Sargent wrote: > And I believe Jonathon confirmed that you could, with the caveat that > you must select from within the transaction. I don't see that you've > laid that out your connection stategy so ymmv. I didn't see Jonathon response? Must be a direct email to me and not to the list. But thanks that helps. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can I read the data without commit
John Fabiani wrote: > Yes I understand - but I want to know is it possible? Can I read the data I > just inserted without a commit. > [...] Are you talking about a web application? Then no, you'll have to code that yourself. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
