Re: [GENERAL] Need help to dynamically access to colomns in function!
If you need them one by one why fetch them into tmprec? Take a look at
the docs for FETCH:
http://www.postgresql.org/docs/8.3/static/sql-fetch.html
especially the FETCH ABSOLUTE...
regards,
Ivan Pavlov
On Dec 16, 3:37 pm, aesthete2...@gmail.com ("Иван Марков") wrote:
> Hello. I have table classif with columns:
> ... , group1, group2, group3, ... , group48, ...
>
> In function i do query and want run on every row and dynamically operate on
> columns from group1 to group20. I do something like this:
>
> OPEN curs FOR select * from classif;
> loop
> fetch curs into tmprec;
> exit when not found;
>
> for I in 1..20 loop
> ...
> -- problem code is:
> value := tmprec.group{I};
> -- i cannot dynamically access to group1, group2, ... colomns according
> to "I" variable.
> ...
>
> end loop;
> end loop;
>
> I have to manually identify and handle each entry without a cycle do
> something like this:
> value := tmprec.group1;
> ...
> value := tmprec.group2;
> ...
> value := tmprec.group2;
> ...
> value := tmprec.group20;
>
> Please help me to do it dynamically with a loop, depending on the I?
> something like this in a loop:
> value := tmprec.group{I};
>
> Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help to dynamically access to colomns in function!
Please disregard my other message. I didn't get what you are trying to do at first. You can do this with dynamic SQL: Look at 38.5.4. Executing Dynamic Commands (http://www.postgresql.org/docs/current/static/plpgsql- statements.html). I guess something like EXECUTE 'SELECT tmprec.group' || i INTO value will work in a loop. Didn't try it though. regards, Ivan Pavlov -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logg errors during UPDATE
Neiter LOG ERRORS nor REJECT LIMIT are implemented in PostgreSQL, though I agree they may be useful. Both can be simulated with a custom stored procedure which loops over a cursor and updates row-by-row, trapping errors along the way. This will, of course, be slower. regards, Ivan Pavlov On Dec 12, 4:34 am, spam_ea...@gmx.net (Thomas Kellerer) wrote: > Hi, > > with Oracle I have the ability to tell the system to log errors during a long > transaction into a separate table and proceed with the statement. This is > quite handy when updating large tables and the update for one out of a > million rows fails. > > The syntax is something like this: > > UPDATE > LOG ERRORS INTO target_log_table; > > Any row that can not be updated will logged into the specified table (which > needs to have a specific format of course) and the statement continues. You > can add a limit on how many errors should be "tolerated". > This works for INSERT and DELETE as well. > > Is there something similar in Postgres? Or a way how I could simulate this? > > Cheers > Thomas > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger/Rules Order of operations
I can't answer your question but I think you may have a serious database design issue at hand. Why not try to accomplish your goals in a simpler way? Regards, Ivan Pavlov On Dec 15, 12:49 pm, ket...@ketema.net (Ketema Harris) wrote: > I am interested in finding out the pros, cons, pitfalls of using the > following design: > > Manual insert into Table A. > Table A has a BEFORE INSERT trigger that causes an insert to table B. > Table B has an AFTER INSERT trigger that causes an insert back to > table A (With different criteria not an endless loop) > > Table A will have its Before Trig fire again and this time the > criteria causes it to finish with a return new. > > Will the second insert into table A commit before the first insert > into table A? What order does the insert into table B finish up? > > Ketema J. Harriswww.ketema.net > ket...@ketema.net > ketemaj on iChat > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about hstore
I installed the hstore module (http://www.postgresql.org/docs/current/ static/hstore.html) because I have a situation where I prefer to use hstore instead of XML. Everything works great, but I saw that after installing the module I actually have two new datatypes: hstore and ghstore. The last one is not documented. So my question is: what is the difference between hstore and ghstore? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Call for translators
Peter Eisentraut wrote: > As the release of PostgreSQL 8.1 draws near, it is once again time to update > the translations of the program messages. To check the status of your > language, check out this web site: > > http://developer.postgresql.org/~petere/nlsstatus/ > > To participate in the translation effort, please follow the instructions set > out on that page, or contact me directly if you have further questions. > Is there any particular reason why there is no translation in Bulgarian? If it's only the lack of contributors, you can count me as the first one. Ivan Pavlov ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] error in SELECT from store procedure
Thanks for the clarification. The problem turned out to be more trivial.
I was not doing something like: employee.f1 := 'value of f1 column'; as
you suggested, but declaring "employee" as record I did not pay
attention to the fact that I use a scheme named employee in the store
proc. My guess is that this caused the error I mentioned.
Thanks once again for your help
Ivan Pavlov
Michael Fuhr wrote:
> On Mon, Oct 10, 2005 at 06:43:25PM +0300, Ivan Pavlov wrote:
>
>>I have a store procedure which returns a record; the query is defined as:
>>
>>select * from spec_proc.view_empl_1('bg',2) AS (f1 varchar, f2 varchar,
>>f3 date, f4 varchar, f5 varchar,f6 varchar,f7 varchar, f8 varchar, f9
>>int, f10 varchar, f11 varchar, f12 varchar, f13 int, f14 varchar, f15
>>date, f16 date)
>>
>>I recieve the following error:
>>
>>ERROR: record "employee" is not assigned yet
>>DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
>>
>>The record "employee" is the record returned by the store procedure.
>>It is created after all other data are assigned to variables.
>>Any ideas what might cause this?
>
>
> Without seeing the function's code we can only guess. My first
> guess is that a simplified version of the function would look
> like this:
>
> CREATE FUNCTION foo() RETURNS SETOF record AS $$
> DECLARE
> employee record;
> BEGIN
> employee.f1 := 'value of f1 column';
> RETURN NEXT employee;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> Calling this function yields the same error you're getting:
>
> SELECT * FROM foo() AS (f1 varchar);
> ERROR: record "employee" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
> CONTEXT: PL/pgSQL function "foo" line 4 at assignment
>
> See "Record Types" in the PL/pgSQL documentation for the reason:
>
> http://www.postgresql.org/docs/8.0/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
>
> "The substructure of a record variable can change each time it is
> assigned to. A consequence of this is that until a record variable
> is first assigned to, it has no substructure, and any attempt to
> access a field in it will draw a run-time error."
>
> I'd guess you're making an assignment to a particular field instead
> of to the record variable as a whole, so PL/pgSQL doesn't know what
> the record structure should be. If that's the case, consider
> creating a composite type and declaring employee to be of that type,
> and perhaps also declare the function to return that type. Another
> possibility would be to assign employee via a SELECT INTO statement.
>
---(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
[GENERAL] error in SELECT from store procedure
I have a store procedure which returns a record; the query is defined as:
select * from spec_proc.view_empl_1('bg',2) AS (f1 varchar, f2 varchar,
f3 date, f4 varchar, f5 varchar,f6 varchar,f7 varchar, f8 varchar, f9
int, f10 varchar, f11 varchar, f12 varchar, f13 int, f14 varchar, f15
date, f16 date)
I recieve the following error:
ERROR: record "employee" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
The record "employee" is the record returned by the store procedure.
It is created after all other data are assigned to variables.
Any ideas what might cause this?
I.P.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
