[SQL] Help on function creating
Hello everyone, I'm using postgresql form an year and I have some
difficult to write a function to make an report. I'm running PostgreSQL
7.5.3 on i686-pc-linux-gnu (Mandriva 10.1). My table structure is:
Tabela "public.matricula"
Column | Type |Modifiers
-+--+-
id | integer | not null default
nextval('seq_matricula'::text)
ref_contrato| integer |
ref_pessoa | integer |
ref_campus | integer |
ref_curso | integer |
ref_periodo | character varying(10)|
ref_disciplina | integer |
ref_curso_subst | integer |
ref_disciplina_subst| integer |
ref_disciplina_ofer | integer |
nota| double precision | default 0
nota_exame | double precision | default 0
nota_final | double precision | default 0
conceito| character varying(5) | default
''::character varying
conceito_exame | character varying(5) | default
''::character varying
conceito_final | character varying(5) | default
''::character varying
I need to have an output like:
ref_pessoa | ref_disciplina1 | ref_disciplina2 | ... | ref_disciplina n
1 | nota_final1 | nota_final2 | ... | nota_final n
How can I make this ? I know that I need to declare an variable an than
make an for - loop, but I don't know how. Where can I get some examples
for this?
Thanks.
--
__
Alguns caminham pelo arco,
eu caminho pela reta.
Alexandre Gonçalves Jacarandá
Assessor de Tecnologia de Informação
Tel.: 0 ** 21 8131-2313
___
Yahoo! doce lar. Faça do Yahoo! sua homepage.
http://br.yahoo.com/homepageset.html
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] constrains problem...
Hy list, i would like to ask you for help. I have 3 tables. table1 has primary key 't1code' and table_rel is 'many to many' relation for table2. table2 primary key is 't2code' table_rel has two fields: 't1code' and 't2code', where both of them together make a primary key for the relation table. This is done so no double values exist. What i would like to do is create a trigger or a constraint which would on delete of an entry from table1 also deleted all entries containing code from table1 in table_rel. When i try to put a constraint via PgAdmin3 it says it can't do it because 't1code' is not primary key or unique. I understand the error, but what i would like to know is how to delete all fields from table_rel containing the 'table1' vaule being equal to 'code' being deleted from table1. Thanks a lot for your anwser! Yure ---(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
Re: [SQL] Database with "override" tables
Lane Van Ingen wrote: Not quite sure how to answer this, but one thought does occur to me: I was perhaps assuming that an override table would override an entire record in the 'original' table(that is what we are doing), and we require that critical fields in the override field be NOT NULL (and in some cases, provide DEFAULT values). Will that help? I fought with the UNION method for a while before backing off to my extremely-long-query system with a FULL JOIN: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; It's not so bad until you get to cross-reference other tables that have override tables, but then it just becomes pretty ugly. So I'll stick with that for now. If the override table did indeed override entire records in the original table, the UNION would have worked beautifully and probably made things cleaner. Thanks for the advice. Mike. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to increase row deletion efficiency?
Alexander Stanier <[EMAIL PROTECTED]> writes: > I am currently trying to separate two environments contained in one > database. Essentially I need to break that one database down into two > with a portion of the data going to each new database. I am intending to > achieve this by duplicating the database and then stripping out the data > that is not required in each database. I have started by trying to > delete data from a set of 28 related tables, however the performance > appears to be terrible. I am deleting from a table called document which > cascades down to 27 tables underneath it linked by various cascading > foreign key constraints. Some of these subsidiary tables have as many as > a couple of million records. Do you have indexes on the referencing columns? PG enforces an index on the referenced column, but not on the other end, and DELETE is where it will hurt if you haven't got one. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] How to increase row deletion efficiency?
I am currently trying to separate two environments contained in one database. Essentially I need to break that one database down into two with a portion of the data going to each new database. I am intending to achieve this by duplicating the database and then stripping out the data that is not required in each database. I have started by trying to delete data from a set of 28 related tables, however the performance appears to be terrible. I am deleting from a table called document which cascades down to 27 tables underneath it linked by various cascading foreign key constraints. Some of these subsidiary tables have as many as a couple of million records. Before executing the delete statement from document I tried setting all constraints as deferred within a transaction, but this does not seem to have helped. I can't work out whether the indexes on these tables are a help or a hindrance. Presumably, any involving the foreign keys should help as long as PostgreSQL will actually use them, but given that large numbers of records are being deleted the query planner may decide just to do a sequence scan. An EXPLAIN doesn't show me what it does past the delete from document, i.e. if indexes are used when cascading. The downside of the indexes is that they have to be maintained which could be a lot of work in large scale deletions. What I fear is that for every row that is deleted from the document table, the database is visiting all subsidiary tables to delete all data related to that one row before returning to document to delete another row. this would mean that all tables are being visited many times. If this is the way it is working, then the large tables are going to be a real problem. The most efficient way to do it would be to delete all document records, then with that list of documents in mind go on to the next table and delete all related records so that each table is only visited once to delete all the relevant records. I was hoping that setting constraints deferred would achieve this. Can anyone advise me on how PostgreSQL (v8.0.3 on MacOS X 10.3) works in a delete statement and what strategy it uses to remove the data? Can I specify "Unrecoverable" so that it doesn't write redo? Are they any indicators I can use to tell me what part of the delete is taking so much time? Also can anyone suggest anything else I can do to speed things up? Or perhaps it simply is a lot of work and there is no way round it. My fallback option is to SELECT data that I do need rather than DELETE the data that I don't, but this route means I cannot make use of the foreign keys. Regards, Alex Stanier. ---(end of broadcast)--- TIP 1: 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] constrains problem...
Jure Kodzoman wrote: What i would like to do is create a trigger or a constraint which would on delete of an entry from table1 also deleted all entries containing code from table1 in table_rel. When i try to put a constraint via PgAdmin3 it says it can't do it because 't1code' is not primary key or unique. I understand the error, but what i would like to know is how to delete all fields from table_rel containing the 'table1' vaule being equal to 'code' being deleted from table1. You put two foreign keys ON table_rel, referencing table1 and table2. Set both to delete on cascade and you're done. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help on function creating
Have you considered using a view to create the report?
AaronOn 12/7/05, Alexandre Gonçalves Jacarandá <[EMAIL PROTECTED]> wrote:
Hello everyone, I'm using postgresql form an year and I have somedifficult to write a function to make an report. I'm running PostgreSQL7.5.3 on i686-pc-linux-gnu (Mandriva 10.1). My table structure is:Tabela "
public.matricula"
Column |
Type
|Modifiers-+--+-
id |
integer |
not null defaultnextval('seq_matricula'::text)
ref_contrato|
integer |
ref_pessoa |
integer |
ref_campus |
integer |
ref_curso
|
integer |
ref_periodo
| character varying(10)|
ref_disciplina |
integer |
ref_curso_subst |
integer |
ref_disciplina_subst|
integer |
ref_disciplina_ofer |
integer |
nota|
double precision |
default 0
nota_exame |
double precision |
default 0
nota_final |
double precision |
default 0
conceito|
character varying(5) | default''::character varying
conceito_exame |
character varying(5) | default''::character varying
conceito_final |
character varying(5) | default''::character varyingI need to have an output like:ref_pessoa | ref_disciplina1 | ref_disciplina2 | ... | ref_disciplina n1 | nota_final1
| nota_final2 | ... | nota_final nHow can I make this ? I know that I need to declare an variable an thanmake an for - loop, but I don't know how. Where can I get some examplesfor this?Thanks.
--__Alguns caminham pelo arco,eu caminho pela reta.Alexandre Gonçalves JacarandáAssessor de Tecnologia de InformaçãoTel.: 0 ** 21 8131-2313
___Yahoo! doce lar. Faça do Yahoo! sua homepage.http://br.yahoo.com/homepageset.html---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Error from trigger
Hello, I have a trigger that will delete records referring to an "events" table upon deletion. I have used it without problems for a number of times: CREATE OR REPLACE FUNCTION delete_event_cascade() RETURNS TRIGGER AS $$ BEGIN DELETE FROM event_citations WHERE event_fk = OLD.event_id; DELETE FROM participants WHERE event_fk = OLD.event_id; RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE TRIGGER delete_event_cascade BEFORE DELETE ON events FOR EACH ROW EXECUTE PROCEDURE delete_event_cascade(); The event_citations table is a cross-reference between events and sources, and goes like this: CREATE TABLE event_citations ( event_fkINTEGER REFERENCES events (event_id), source_fk INTEGER REFERENCES sources (source_id), PRIMARY KEY (event_fk, source_fk) ); I'm doing a little cleanup, and perform this query: pgslekt=> select * from event_citations where source_fk=553; event_fk | source_fk --+--- 2600 | 553 2592 | 553 2817 | 553 19919 | 553 19920 | 553 (5 rader) Then I do an adjustment in the "sources" table: pgslekt=> update sources set parent_id=553,small_text='',large_text='23.04.1745 ved Solum: \"Abraham Nielsøn Aafos 49 aar 5 dage\"' where source_id=554; UPDATE 1 And then, as I try to delete event #2600, I get the following message: pgslekt=> delete from events where event_id=2600; ERROR: relation with OID 1141502 does not exist CONTEXT: SQL statement "DELETE FROM event_citations WHERE event_fk= $1 " PL/pgSQL function "delete_event_cascade" line 2 at SQL statement I run a quick check: pgslekt=> select * from event_citations where event_fk=2600; event_fk | source_fk --+--- (0 rader) The record seems to have disappeared into thin air. There has not been performed any inserts or updates in the database between the 'update' and the 'delete' above. And the event won't go away. This is hardly a practical problem, because an event that isn't linked to a "person" through the "participants" table will never print anywhere, and the referring "participant" disappeared. But I don't like it anyway. I'm running PostgreSQL 8.0.4. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Error from trigger
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > I have a trigger that will delete records referring to an "events" table > upon deletion. I have used it without problems for a number of times: > CREATE OR REPLACE FUNCTION delete_event_cascade() RETURNS TRIGGER AS $$ > BEGIN > DELETE FROM event_citations WHERE event_fk = OLD.event_id; > DELETE FROM participants WHERE event_fk = OLD.event_id; > RETURN OLD; > END; > $$ LANGUAGE plpgsql; > CREATE TRIGGER delete_event_cascade > BEFORE DELETE ON events > FOR EACH ROW EXECUTE PROCEDURE delete_event_cascade(); > The event_citations table is a cross-reference between events and > sources, and goes like this: > CREATE TABLE event_citations ( > event_fkINTEGER REFERENCES events (event_id), > source_fk INTEGER REFERENCES sources (source_id), > PRIMARY KEY (event_fk, source_fk) > ); Is there a reason you don't just mark the FK reference as ON DELETE CASCADE, rather than using a handwritten trigger? > And then, as I try to delete event #2600, I get the following message: > pgslekt=> delete from events where event_id=2600; > ERROR: relation with OID 1141502 does not exist > CONTEXT: SQL statement "DELETE FROM event_citations WHERE event_fk= $1 " > PL/pgSQL function "delete_event_cascade" line 2 at SQL statement Offhand this looks like you might have dropped and recreated the event_citations table? If so it's just the known problem that plpgsql caches plans and doesn't throw them away when the referenced objects change. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error from trigger
On Thursday 08 December 2005 00:23, Tom Lane wrote: >Is there a reason you don't just mark the FK reference as ON DELETE >CASCADE, rather than using a handwritten trigger? I could have done that, of course. I'm still a little shaky on "best practice" with these things. Besides, I haven't found out yet how to alter the table to make the reference cascading. And I wanted to experiment with simple triggers. >Offhand this looks like you might have dropped and recreated the >event_citations table? If so it's just the known problem that >plpgsql caches plans and doesn't throw them away when the referenced >objects change. Right on target. Thank you. A few days ago, as I have already related on this list, I did the following to fix a bad design with inherited tables: pgslekt=> create table event_cits ( pgslekt(> event_fk integer references events (event_id), pgslekt(> source_fk integer references sources (source_id), pgslekt(> PRIMARY KEY (event_fk, source_fk) pgslekt(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "event_cits_pkey" for table "event_cits" CREATE TABLE pgslekt=> insert into event_cits (select event_fk, source_fk from event_citations); INSERT 0 29139 pgslekt=> drop table event_citations cascade; NOTICE: drop cascades to rule _RETURN on view event_notes NOTICE: drop cascades to view event_notes DROP TABLE pgslekt=> drop table citations; DROP TABLE pgslekt=> alter table event_cits rename to event_citations; ALTER TABLE pgslekt=> \i views_and_functions.sql I've run a VACUUM FULL ANALYZE, but that doesn't seem to cure the situation. Is there a way to fix it, short of a full dump, drop, and reload? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Locale and pattern matching
A question about encoding, some days ago, led me to concentrate my attention on the subject and perform a few tests on two different 8.1 installations, configured as follows: Machine 1 - pt_PT(Portuguese_Portugal.28591)/LATIN1 Machine 2 - pt_PT(Portuguese_Portugal.28605)/LATIN9 In both machines, case insensitive pattern matching designed to match the letter 'A' will indeed match 'A' and 'a' but not the accented forms. Generalizing, an accented or unaccented vowel in the pattern will only match that vowel in upper or lowercase forms if the accent is the same as specified in the pattern. For instance, if the pattern specifies an uppercase 'A' with an accute accent, then it will match a lowercase 'A' with an accute accent, but not an upper or lower case 'A' with a tilde. This behaviour seems inconsistent with that of the ORDER BY clause, which considers all forms of a vowel as equal (uppercase/lowercase,accented/not accented). Shouldn't case insensitive pattern matching follow the same collation weights that ORDER BY uses ? Can someone please elaborate on this subject ? Another question, for european users: Until now, I've been selecting LATIN1 encoding, but after a few tests, I came to think that LATIN9 is a better option (the euro sign...). For those who regularly use LATIN9, what is your opinion ? Is it indeed a better option ? Thank you. Hélder M. Vieira LATIN CAPITAL LETTER A WITH GRAVE ---(end of broadcast)--- TIP 1: 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] dow question
Hi All, I am working on a query which in part is CASE WHEN extract(dow from tbl_detail.ship_by_date) = 0 THEN 'Sunday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 1 THEN 'Monday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 2 THEN 'Tuesday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 3 THEN 'Wednesday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 4 THEN 'Thursday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 5 THEN 'Friday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 6 THEN 'Saturday ' || tbl_detail.ship_by_date::text END AS sort_by_string Is there a better way to do this? The CASE seems inefficient and wordy but perhaps not. I was hoping for day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS sort_by_string but AFAIK day_name(date) or something like it is merely my fantasy. ;-) -- Kind Regards, Keith ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] dow question
On Dec 8, 2005, at 11:03 , Keith Worthington wrote: Is there a better way to do this? The CASE seems inefficient and wordy but perhaps not. I was hoping for day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS sort_by_string but AFAIK day_name(date) or something like it is merely my fantasy. ;-) Will to_char() do what you want? Something like test=# select to_char(current_date, 'Day -MM-DD'); to_char -- Thursday 2005-12-08 (1 row) http://www.postgresql.org/docs/current/interactive/functions- formatting.html Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] dow question
Michael Glaesemann wrote: On Dec 8, 2005, at 11:03 , Keith Worthington wrote: Is there a better way to do this? The CASE seems inefficient and wordy but perhaps not. I was hoping for day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS sort_by_string but AFAIK day_name(date) or something like it is merely my fantasy. ;-) Will to_char() do what you want? Something like test=# select to_char(current_date, 'Day -MM-DD'); to_char -- Thursday 2005-12-08 (1 row) http://www.postgresql.org/docs/current/interactive/functions- formatting.html Michael Glaesemann grzm myrealbox com Michael, That will do nicely. I didn't realize until now after your tip and reading the to_char documentation how much I could do with to_char. to_char(tbl_detail.ship_by_date, 'FMDay, /MM/DD') AS ship_by_string, is exactly what the doctor ordered. Thanks for the tip. -- Kind Regards, Keith ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error from trigger
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > On Thursday 08 December 2005 00:23, Tom Lane wrote: >> Offhand this looks like you might have dropped and recreated the >> event_citations table? If so it's just the known problem that >> plpgsql caches plans and doesn't throw them away when the referenced >> objects change. > Right on target. Thank you. A few days ago, as I have already related on > this list, I did the following to fix a bad design with inherited > tables: > ... > I've run a VACUUM FULL ANALYZE, but that doesn't seem to cure the > situation. Is there a way to fix it, short of a full dump, drop, and > reload? Just starting a fresh session should make the problem go away, or if that's not practical update the function definition using ALTER FUNCTION or CREATE OR REPLACE FUNCTION. (You don't need to actually *change* anything about the function, just issue a command that could change it.) If that doesn't make the error go away then we need to look more closely at what's causing it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] cannot alter column data type to "date"
Below is the message I receive when I try to alter the data type of a column to "date". I had to drop the column and add it again just so I can set its data type to "date". How do I change a column's data type to "date" without dropping and then creating it again? Hoping for a kind response. Thank you. SQL error: ERROR: column "date_signed" cannot be cast to type "date" In statement: ALTER TABLE "canvass" ALTER COLUMN "date_signed" TYPE date -- ___ Search for businesses by name, location, or phone number. -Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10 ---(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
Re: [SQL] cannot alter column data type to "date"
On Thu, Dec 08, 2005 at 03:12:07PM +0800, Noel Doydora wrote: > Below is the message I receive when I try to alter the data type of > a column to "date". I had to drop the column and add it again just > so I can set its data type to "date". How do I change a column's > data type to "date" without dropping and then creating it again? See the USING clause in the ALTER TABLE documentation: http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
