Re: [SQL] Casting with character and character varying
> > Hi all. > Recently I face some problem with casting character type variable and > varchar variable. > The situation was like: I had 2 table, on table A, the user_name is defined > as character(32), and table B uses varchar(32). I have 1 function and a > trigger to manipulate with these data. > > Here's the function: (NEW = tableB) > -- > create or replace function prepaid () returns trigger as ' > declare Rec tableA%ROWTYPE; > > begin > if NEW.status != 2 then >return NEW; > else > select into Rec * from tableA where user_name = trim(trailing '' '' from > cast(NEW.user_name as varchar)) and user_type = ''T''; >if not found then > return NEW; >end if; > >insert into temptable values (tableA.FieldA); > end if; > return NEW; > end; > ' language 'plpgsql'; > - > supposingly the insert will insert the value of field A in table into > temptable (declare as varchar(100)), instead of inserting single row, the > insert actually insert all data from tableA to temptable (if there's 10 row > in tableA, the insert statement will insert all to temptable), that's weird. > > Then i tried with cast(trim(trailing '' '' from NEW.user_name)::varchar as > text), and it's returns me with nothing (suppose there'll be 1 record > matched). > Don't know what's actually right now: If tableA uses character and tableB varchar, you'll have to trim the user_name from tableA not tableB, because varchar is already trimmed. But what you're doing within the function code is trimming a varchar field. Second is, what is "tableA.FieldA"? Is it a column name of tableA? Looks that way, because I can't see a variable of this name. I'm not sure what happens on an insert statement like this, but it's very well possible this causes all row-columns FieldA from tableA to be inserted into temptable. And it would be useful to see the CREATE TRIGGER statement too. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Listing Users
What is the SQL command to list users? CREATE USER, DROP USER, ALTER USER, USER I just can't seem to find the command to list them. -Kyle ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] integrity of column used to order rows
hello, I was hoping someone might be able to help me with this problem... I have a table that is essentially joined back to a parent table. I have a column in the child table called "rank" that is a simple integer data type, used to indicate the order that the child elements should be displayed in. What I want to be able to do is make sure that at all times the child records linked to a parent record have values for the "rank" field that are consecutive starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7). Can someone offer the best suggestion to do this? Should I write a PL/pgsql function and add it as a column constraint to check to make sure the numbers are consecutive? Can I use some kind of trigger that will execute a function and "automatically" give the fields the correct number? Would this seriously impact the performance since it would have to go through what might become a large table only to work on a small part of it (ie, records with the same parent_id)? I have a lot of experience with mySQL but a lot of these more sophisticated pgSQL features are a little tough for me to get a handle on immediately... thanks very much. -Cliff ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] trouble with query
On Tue, Mar 18, 2003 at 03:47:55PM +0100, alexj wrote:
> Hi,
>
> I'm looking how can I do to insert multiple values from a
> complexe query.
>
> What I want to do is something like that :
>
> INSERT INTO est_planifie_jour (id,ref_activite,ref_ressource,ref_jour)
> (SELECT nextval('est_p_id_p') FROM est_planifie),
> (SELECT id_activite FROM activite WHERE
> nom = 'SGBD 02 cours théorique),
> (SELECT ref_ressource FROM personne,groupe,fait_partie
> WHERE groupe.nom ='cycle 1b' AND
> fait_partie.ref_groupe = groupe.id_groupe AND
> personne.id_personne = fait_partie.ref_personne),
> (SELECT id_jour FROM jour WHERE jour.id_jour = 3)
>
You should rewrite your insert to receive multiple rows from single
select. This query shoud be similiar to this below:
insert into ... (..)
select
nextval('est_p_id_p'),
id_activite,
ref_resource,
ref_jour
from
activite, personne,groupe,fait_partie,jour
where
activite.nom='..' and fait_partie.ref_groupe=groupe.id_groupe
and groupe.nom='..'
and jour.id_jour=3
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] the best way to get some records not in another table
Hi, According to the following report, I think using "except" would be the best way to do. Thank you! Jack EXPLAIN SELECT DISTINCT a.c1 FROM test_j2 a WHERE a.c1 NOT IN (SELECT DISTINCT b.c1 FROM test_j1 b); Unique (cost=54544.91..54547.41 rows=50 width=6) -> Sort (cost=54544.91..54546.16 rows=500 width=6) Sort Key: c1 -> Seq Scan on test_j2 a (cost=0.00..54522.50 rows=500 width=6) Filter: (subplan) SubPlan -> Materialize (cost=54.50..54.50 rows=100 width=6) -> Unique (cost=0.00..54.50 rows=100 width=6) -> Index Scan using test_j1_pkey on test_j1 b (cost=0.00..52.00 rows=1000 width=6) (9 rows) === EXPLAIN SELECT a.c1 FROM test_j2 a EXCEPT SELECT b.c1 FROM test_j1 b; SetOp Except (cost=149.66..159.66 rows=200 width=6) -> Sort (cost=149.66..154.66 rows=2000 width=6) Sort Key: c1 -> Append (cost=0.00..40.00 rows=2000 width=6) -> Subquery Scan "*SELECT* 1" (cost=0.00..20.00 rows=1000 width=6) -> Seq Scan on test_j2 a (cost=0.00..20.00 rows=1000 width=6) -> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000 width=6) -> Seq Scan on test_j1 b (cost=0.00..20.00 rows=1000 width=6) (8 rows) = EXPLAIN SELECT DISTINCT a.c1 FROM test_j2 a WHERE NOT EXISTS (SELECT b.c1 FROM test_j1 b WHERE b.c1 = a.c1) ; Unique (cost=3455.91..3458.41 rows=50 width=6) -> Sort (cost=3455.91..3457.16 rows=500 width=6) Sort Key: c1 -> Seq Scan on test_j2 a (cost=0.00..3433.50 rows=500 width=6) Filter: (NOT (subplan)) SubPlan -> Index Scan using test_j1_pkey on test_j1 b (cost=0.00..17.07 rows=5 width=6) Index Cond: (c1 = $0) (8 rows) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Is it possible to select encoding in PLPGSQL function?
Is it possible? Thank you! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] integrity of column used to order rows
On Wednesday March 19 2003 9:18, [EMAIL PROTECTED] wrote: > > What I want to be able to do is make sure that at all times the child > records linked to a parent record have values for the "rank" field that > are consecutive starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7). > > Can someone offer the best suggestion to do this? This is certainly possible via a triggered PL/pgSQL function. Of course, whether or not it is feasible w/r/t performance or exactly how you choose to set the ranks depends on your context. If you have appropriate indices on the foreign key to the parent table, the overall size of the table is probably irrelevant; use of the index would allow you to avoid traversal of the entire table. You also have the option of writing the function in C to get better performance. Ed ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Listing Users
On Wed, 19 Mar 2003, Kyle wrote: > What is the SQL command to list users? > > CREATE USER, DROP USER, ALTER USER, USER > > I just can't seem to find the command to list them. SELECT * from pg_user ; > > -Kyle > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] howto/min values
Hi, My postings seem to double on me. Well thanks to everybody that answered. They were not the answers I was hoping for but, I found one in the docs. Let me explain again what I'm doing with a example: table: points id distance 1 25 0.26598333 1 32 0.65983600 1 56 1.23692000 2 96 120.26659366 2 32 0.54865130 etc Now select for point 1 where id = min(distance). The trick I was suspecting but not been able to grasp is the use of aliases. In Bruce Momjian's "Postgresql: introduction and limitations" was an example like this with structure from the problem here: select p.points, p.id, p.distance from table as p where distance = (select min(distance) from table as p1 where p.points=p1points) order by points, id Now this works. So going to the next question, if distance is a calculated field how can I use it in the subquery. How do you add another alias in: select p.points, p.id, (x-x) as distance from table as p where distance = (select min(distance) from table as p1 where p.points=p1points) order by points, id This of course doesn't work, because of the referring to the calculated field wich is not actually a part of the table. Can you do this somehow or is it impossible? Love to hear from you guys, greetz Sjors
Re: [SQL] vacuum all but system tables
> I'd like to make a script to automatically vacuum all my DBs nightly. And > I'd like to skip system tables, starting with pg_*. If you run the process as non-super user, it must skip your system tables. On the other hand, if you run it as a super user, it must be done table wise from your script. regards, bhuvaneswaran ---(end of broadcast)--- TIP 3: 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] Number of rows affected by an update
Uz.ytkownik David Witham napisa?: Hi, Is there a way of capturing how many rows a select, delete or update affect from within a PL/pgSQL function? Read Postgres documentation. 19.5.5. Obtaining result status GET DIAGNOSTICS var_integer = ROW_COUNT; Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] vacuum all but system tables
Hello. I'd like to make a script to automatically vacuum all my DBs nightly. And I'd like to skip system tables, starting with pg_*. I've seen in the list archives somebody's solution using regexps, something like: $> vacuum analyze !~ ^pg_; It doesn't work for me, and I cannot find the original post I've seen some time ago. I'm running: `PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3'. Maybe someone came across with this and could help? -- Victor Yegorov pgp0.pgp Description: PGP signature
Re: [SQL] howto/min values
> My postings seem to double on me. It's standard procedure to reply twice, one to the list and one to the sender, because you don't need to subscribe to the list to get the answers. But if you are subscribed and ask a question you usually get two. > > Now this works. So going to the next question, if distance is a calculated = > field how can I use it in the subquery. How do you add another alias in: > > select p.points, p.id, (x-x) as distance=20 > from table as p > where distance =3D (select min(distance)=20 > from table as p1 > where p.points=3Dp1points) > order by points, id > =20 > This of course doesn't work, because of the referring to the calculated fie= > ld wich is not actually a part of the table. Can you do this somehow or is = > it impossible? > No, not at all. Something like select p.points, p.id, p.distance from (select points, id, (x-x) as distance from table) as p where p.distance = (select min(p1.distance) > from table as p1 > where p.points=p1.points) > order by p.points, p.id ; should do the trick. The FROM clause allows any kind of subqueries within parentheses. Regards, Christoph ---(end of broadcast)--- TIP 3: 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] vacuum all but system tables
"Victor Yegorov" <[EMAIL PROTECTED]> writes: > I'd like to make a script to automatically vacuum all my DBs nightly. And > I'd like to skip system tables, starting with pg_*. Um ... what in the world makes you think that's a good idea? System tables need maintenance too. regards, tom lane ---(end of broadcast)--- TIP 3: 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] vacuum all but system tables
"Victor Yegorov" <[EMAIL PROTECTED]> writes: > May be I'll put my question in a different manner: > System tables are location-wide (I mean one set of tables for PostgreSQL > location) or each database has it's own set of system tables? pg_database, pg_shadow, pg_group are shared, the rest are per-database. These three are generally not so large that it's worth worrying about vacuuming them extra times ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
