[SQL] SELECTing into usertype, how to do it?
I have a type, declared like this: CREATE TYPE type_a AS ( member_a varchar, member_b bool ); There is also a table: CREATE TABLE table_a ( col_a varchar, col_b bool, col_c int4 ); Now, in a function, I declared type_var variable of type type_a: DECLARE type_var type_a; And then, when I want to fill in the type_var, i do this: type_var.member_a := col_a FROM table_a WHERE col_c = 5; type_var.member_b := col_b FROM table_a WHERE col_c = 5; Is there a way to fill in the type_var, but from just one statement? Here I have two 'selects' on table_a, and that seems as bit expensive when iterated a lot of times. I guess I could do: SELECT col_a, col_b INTO type_var.member_a, type_var.member_b FROM table_A WHERE col_c = 5; but that is a bit hard to read :) Are there more options on doing what I'd like to do? Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECTing into usertype, how to do it?
am 25.04.2006, um 14:03:07 +0200 mailte Mario Splivalo folgendes: > > And then, when I want to fill in the type_var, i do this: > > type_var.member_a := col_a FROM table_a WHERE col_c = 5; > type_var.member_b := col_b FROM table_a WHERE col_c = 5; > > Is there a way to fill in the type_var, but from just one statement? > Here I have two 'selects' on table_a, and that seems as bit expensive > when iterated a lot of times. > > I guess I could do: > SELECT col_a, col_b INTO type_var.member_a, type_var.member_b FROM > table_A WHERE col_c = 5; > > but that is a bit hard to read :) > > Are there more options on doing what I'd like to do? select into type_var col_a, col_b from table_a ... ; HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECTing into usertype, how to do it?
On Tue, 2006-04-25 at 14:21 +0200, A. Kretschmer wrote: > am 25.04.2006, um 14:03:07 +0200 mailte Mario Splivalo folgendes: > > > > And then, when I want to fill in the type_var, i do this: > > > > type_var.member_a := col_a FROM table_a WHERE col_c = 5; > > type_var.member_b := col_b FROM table_a WHERE col_c = 5; > > > > Is there a way to fill in the type_var, but from just one statement? > > Here I have two 'selects' on table_a, and that seems as bit expensive > > when iterated a lot of times. > > > > I guess I could do: > > SELECT col_a, col_b INTO type_var.member_a, type_var.member_b FROM > > table_A WHERE col_c = 5; > > > > but that is a bit hard to read :) > > > > Are there more options on doing what I'd like to do? > > select into type_var col_a, col_b from table_a ... ; > Thnx... still, that SELECT INTO looks pretty anoying... it would be neat to type just: type_var := col_a, col_b FROM ... I guess the code would look much more readable... Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(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] SELECTing into usertype, how to do it?
am 25.04.2006, um 14:25:50 +0200 mailte Mario Splivalo folgendes: > > > > select into type_var col_a, col_b from table_a ... ; > > > > Thnx... still, that SELECT INTO looks pretty anoying... it would be neat > to type just: > type_var := col_a, col_b FROM ... well-nigh: type_var := (col_a, col_b) FROM ... Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Migrating a Database to a new tablespace
Hi, Jim, Jim C. Nasby wrote: > That means that the tablespace directory isn't empty. This might be some artifacts from backend kills / crashes, partially during COPY and CREATE DATABASE operations. (It's a developer machine after all). So when pgadmin3 displays no dependencies, can I assume it is safe to empty the tablespace directory manually, and then drop the tablespace? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Migrating a Database to a new tablespace
Markus Schaber <[EMAIL PROTECTED]> writes: > So when pgadmin3 displays no dependencies, can I assume it is safe to > empty the tablespace directory manually, and then drop the tablespace? Instead of assuming anything, why don't you look in the tablespace directory and see what's there? A quick "ls -aR" would give more information than guessing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Migrating a Database to a new tablespace
Hi, Tom, Tom Lane wrote: >>So when pgadmin3 displays no dependencies, can I assume it is safe to >>empty the tablespace directory manually, and then drop the tablespace? > > Instead of assuming anything, why don't you look in the tablespace > directory and see what's there? A quick "ls -aR" would give more > information than guessing. There's plenty of stuff there, 8.8 Gigabytes in total. The question is how to determine if any of those files are still needed, and how to migrate them so I can drop that tablespace. -> ls -aR .: . .. 16406 760866 PG_VERSION ./16406: . 10308 10740 1255 2602 2614 2655 2667 2683 2699 .. 10310 10742 1259 2603 2615 2656 2668 2684 2700 10287 10723 10743 16789 2604 2616 2657 2669 2685 2701 10289 10725 10745 16791 2605 2617 2658 2670 2686 2702 10293 10727 10747 16793 2606 2618 2659 2673 2687 2703 10295 10728 10748 16794 2607 2619 2660 2674 2688 2704 10299 10730 10750 16796 2608 2620 2661 2675 2689 PG_VERSION 10301 10732 10752 16798 2609 2650 2662 2678 2690 pg_internal.init 10302 10733 1247 16800 2610 2651 2663 2679 2691 10304 10735 1248 16801 2611 2652 2664 2680 2692 10305 10737 1249 2600 2612 2653 2665 2681 2693 10307 10738 1250 2601 2613 2654 2666 2682 2696 ./760866: . 2620301718 301891 302062 302231318719 397700 .. 2650301722 301893 302064 302233318721 397702 10287 2651301724 301895 302068 302237319117 397704 10289 2652301728 301899 302070 302239319119 397706 10293 2653301730 301901 302072 302244319122 397707 10295 2654301732 301906 302076 302246319123 397709 10299 2655301736 301908 302078 302248319124 397711 10301 2656301738 301910 302082 302252319125 397715 10302 2657301743 301914 302084 302254319126 397716 10304 2658301745 301916 302086 302258319128 397717 10305 2659301747 301921 302090 302260319129 397718 10307 2660301751 301923 302092 302262319130 397720 10308 2661301753 301925 302096 302262.1 319132 397722 10310 2662301758 301929 302098 302266319133 397752 10723 2663301760 301931 302100 302268319138 397754 10725 2664301762 301936 302104 302273319140 397756 10727 2665301766 301938 302106 302275319141 399801 10728 2666301768 301940 302111 302277319143 399802 10730 2667301773 301944 302113 302281319145 399804 10732 2668301775 301946 302115 302283319146 399806 10733 2669301777 301951 302119 302287319147 399807 10735 2670301781 301953 302121 302289319149 399808 10737 2673301783 301955 302126 302291319151 399809 10738 2674301788 301959 302128 302295319153 399810 10740 2675301790 301961 302130 302297319155 399811 10742 2678301792 301966 302134 302302319185 399812 10743 2679301796 301968 302136 302304319187 399814 10745 2680301798 301970 302141 302306319189 399816 10747 2681301803 301974 302143 302310319193 399817 10748 2682301805 301976 302145 302312319195 399818 10750 2683301807 301981 302149 302317390770 399820 10752 2684301811 301983 302151 302319390772 399822 1247 2685301813 301985 302156 302321390774 399824 1248 2686301818 301989 302158 302325396855 399825 1249 2687301820 301991 302160 302327396857 399827 1250 2688301822 301996 302164 302332396859 399829 1255 2689301826 301998 302166 302334397257 400025 1259 2690301828 302000 302170 302336397258 400026 2600 2691301833 302004 302172 302340397263 400028 2601 2692301835 302006 302174 302342397265 400030 2602 2693301837 302011 302178 302347397267 400037 2603 2696301841 302013 302180 302349397277 400038 2604 2699301843 302015 302185 302351397278 400040 2605 2700301848 302019 302187 302351.1 397280 400042 2606 2701301850 302021 302189 302355397282 400082 2607 2702301852 302026 302193 302357397305 400083 2608 2703301856 302028 302195 302362397306 400085 2609 2704301858 302030 302199 302364397308 400087 2610 301500 301863 302034 302201 302366397310 400483 2611 301502 301865 302036 302203 302370397347 400484 2612 301504 301867 302040 302207 302372397348 PG_VERSION 2613 301505 301871 302042 302209 317537397350 pg_internal.init 2614 301507 301873 302044 302214 317540397352 2615 301509 301877 302048 302216 318418397397 2616 301511 301879 302050 302218 318420397417 2617 301512 301881 302054 30 318553397419 2618 301714 301885 302056 302224 318554397421 2619 301716 301887 302058 302
Re: [SQL] Migrating a Database to a new tablespace
Markus Schaber <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Instead of assuming anything, why don't you look in the tablespace >> directory and see what's there? A quick "ls -aR" would give more >> information than guessing. > There's plenty of stuff there, 8.8 Gigabytes in total. The question is > how to determine if any of those files are still needed, and how to > migrate them so I can drop that tablespace. Match the subdirectory names against pg_database.oid --- any subdir that doesn't correspond to any live entry in pg_database is junk and can be flushed. Within a valid database's subdirectory, match the file names to that database's pg_class.relfilenode (not oid!) to see if any of them are live. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
