Re: [SQL] create type input and output function examples

[Christoph Haller]

> 
> I've seen the docs for create type and an example of the syntax to create a 
> type. What I haven't seen is the functions that are passed for the input and 
> output elements.
> 
> CREATE TYPE box (INTERNALLENGTH = 8,
> INPUT = my_procedure_1, OUTPUT = my_procedure_2);
> 
> Now what would be in my_procedure1 and my_procedure2 procedures? I know they 
> are just example placeholders above but I need to see a real example of what 
> would be in those procedures to better understand what they should do. 
> Thanks.
> 
Pretty late reply, hopefully not too late. 
My understanding is these procedures are C functions. 
For examples do the following:
List the types already in use and their io-functions as 
select typname, typinput, typoutput from pg_type ;
Then search the source code (*.c files) 
for  resp.  

There is a box type already. 
It's box_in and box_out functions can be found in 
$PGSQLD/src/backend/utils/adt/geo_ops.c 

HTH 
Regards, Christoph 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Using UNION inside a cursor

[Paul Ganainm]

[EMAIL PROTECTED] says...


>  (SELECT  * FROM history  WHERE obs_type = \'AA\' )
>  UNION 
>  (SELECT  * FROM  history  WHERE   obs_type = \'TA\');


Maybe I'm just confused here, but what's to stop you using 

SELECT * FROM History WHERE (Obs_Type = \'AA\' AND Obs_Type = \'TA\')


? Or have I missed something really obvious?


Paul...


-- 

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.


---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] query assistance

[Jodi Kanter]

Is there a straight forward way to pull out duplicates in a particular
field given a value in another field?
For example, I have a table that lists users and study names associated
with those users. Each user can have one or more study names. My goal
is to determine if any of these people have duplicate study names.
There are duplicated study names across the system and that is ok. I
just want to see if any users have duplicate study names among their
studies.
My table looks like this:

       Table "public.study"
    Column    |    Type |   
Modifiers 
--+-+--
 sty_pk   | integer | not null default
nextval('pk_seq'::text)
 study_name   | character varying(128)  | not null
 start_date   | timestamp without time zone | 
 sty_comments | text    | 
 created_by   | integer | 
Indexes: study_pkey primary key btree (sty_pk)

I am concerened with study_name. The created_by field tells me who owns
it.
Thanks
Jodi

-- 









___
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
[EMAIL PROTECTED]


 
 
 

Re: [SQL] query assistance

[Richard Huxton]

On Tuesday 04 November 2003 15:16, Jodi Kanter wrote:
> Is there a straight forward way to pull out duplicates in a particular
> field given a value in another field?
> For example, I have a table that lists users and study names associated
> with those users. Each user can have one or more study names. My goal is
> to determine if any of these people have duplicate study names. There
> are duplicated study names across the system and that is ok. I just want
> to see if any users have duplicate study names among their studies.

Something like:

SELECT
  created_by, study_name, count(sty_pk)
FROM
  study
GROUP BY
  created_by, study_name
HAVING
  count(sty_pk) > 1;

-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[SQL] Please help me to slove this SQL statements

[Freshman]

There are three table in database which is suppliers, projects, and
shipments
suppliers contain suppliers id, name ...etc
projects contain project name ..suppliers ID ( J1---J7) ...etc
shipments table contain suppliers ID , PROJECTS ID

how can i query to find out the suppliers to supply all the projects ID



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Error message during compressed backup

[Senthil Kumar S]

Hi Peter Eisentraut,

>>select proowner from pg_proc where proname = 'plpgsql_call_handler';
It gives me an id '101'

While I search for the users in the pg_user, there is no user of id 101
select * from pg_user where usesysid = 101;
No result was fetched.

While I search this way
select * from pg_user where usename like 'postgres'
This give me a result username = postgres usesysid = 1

In comments you said
>> You may want to adjust the owner of the function to a valid user (use
UPDATE).
Do u mean I need to update the table pg_proc, with the following statement
UPDATE pg_proc SET proowner = 1 where proname = 'plpgsql_call_handler';

I am asking this because to ensure not any wrong impact gets into the live
database. Pls advise.

Regards
Kumar

- Original Message - 
From: "Peter Eisentraut" <[EMAIL PROTECTED]>
To: "Senthil Kumar S" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 6:01 PM
Subject: Re: [SQL] Error message during compressed backup


> Senthil Kumar S writes:
>
> > $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f
/home/db_repository/testdb20031023.sql.tar.gz -u -F c
>
> > WARNING: owner of function "plpgsql_call_handler" appears to be invalid
>
> Run
>
> select proowner from pg_proc where proname = 'plpgsql_call_handler';
>
> which gives you the ID of the user that owns this function.  Then run
>
> select * from pg_user;
>
> to get the list of valid users.  You may want to adjust the owner of the
> function to a valid user (use UPDATE).
>
> -- 
> Peter Eisentraut   [EMAIL PROTECTED]
>


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Problems with NEW.* in triggers

[Jamie Lawrence]

Hi folks -

I'm having a problem with what looks like it should be trivial.

For the function

create or replace function timestamp_fn() returns opaque as '
begin
NEW.moddate := coalesce(NEW.moddate, now());
return NEW;
end
' language 'plpgsql';

on an after insert or update trigger, never sets moddate to now().

I had thought that if moddate isn't included in an insert or update,
that it would be null in the NEW context, so that this would fall 
through to the now() call. (I also tried this as below:

create or replace function timestamp_fn() returns opaque as '
 begin
 if NEW.moddate is not null then
 return NEW;
 else
 NEW.moddate := now();
 return NEW;
 end if;
  end
' language 'plpgsql';

With the same results.)

Any thoughts on what I'm doing wrong??

-j


-- 
Jamie Lawrence[EMAIL PROTECTED]
Never eat anything bigger than your head.



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Problems with NEW.* in triggers

[Tom Lane]

Jamie Lawrence <[EMAIL PROTECTED]> writes:
> I had thought that if moddate isn't included in an insert or update,
> that it would be null in the NEW context,

No, it would be whatever the value to be assigned to the column would
be, if the trigger were not present.  In particular, during an UPDATE
it's going to contain the old value of the field.  In an INSERT it would
be whatever the column's default value is.

I am not sure what your intention is here.  If you want the trigger to
force the field to current time, it can certainly do that.  If you want
the user to control whether the field is updated, why do you need a
trigger at all?

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Problems with NEW.* in triggers

[Josh Berkus]

Jamie,

> Any thoughts on what I'm doing wrong??

Yes.  If you want to modify the new data, you need to use a BEFORE trigger.  
AFTER triggers can't modify NEW, just read it.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Problems with NEW.* in triggers

[Jamie Lawrence]

On Tue, 04 Nov 2003, Tom Lane wrote:

> Jamie Lawrence <[EMAIL PROTECTED]> writes:
> > I had thought that if moddate isn't included in an insert or update,
> > that it would be null in the NEW context,
> 
> No, it would be whatever the value to be assigned to the column would
> be, if the trigger were not present.  In particular, during an UPDATE
> it's going to contain the old value of the field.  In an INSERT it would
> be whatever the column's default value is.

For an insert, the default is null in this case.

test=# create table trash (moddate timestamp, message text);
CREATE TABLE
test=# create or replace function timestamp_test() returns opaque as '
test'# begin
test'# NEW.moddate := coalesce(NEW.moddate, now());
test'# return NEW;
test'# end
test'# ' language 'plpgsql';
CREATE FUNCTION
test=# create trigger critter_timestamp_test after insert or update on critter for 
each row execute procedure timestamp_fn();
CREATE TRIGGER
test=# insert into trash (message) values ('hi there');
INSERT 560920 1
test=# insert into trash (message) values ('hi there');
INSERT 560921 1
test=# select * from trash;
 moddate | message  
-+--
 | hi there
 | hi there
(2 rows)

test=# 

I don't understand why moddate isn't getting set to now() in the above.

(Point taken on updates... I was thinking about NEW in slightly
the wrong way for an after trigger.)


> I am not sure what your intention is here.  If you want the trigger to
> force the field to current time, it can certainly do that.  If you want
> the user to control whether the field is updated, why do you need a
> trigger at all?

Excellent question, sigh. I'm trying to bandaid a bad design choice
until the application can be changed.

Now that you have me thinking about it, an update rule is probably a
better idea.

Thanks for the help, I appreciate it -

-j


-- 
Jamie Lawrence[EMAIL PROTECTED]
"Remember, half-measures can be very effective if all you deal with are
half-wits."
   - Chris Klein



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Problems with NEW.* in triggers

[Tom Lane]

Jamie Lawrence <[EMAIL PROTECTED]> writes:
> I don't understand why moddate isn't getting set to now() in the above.

Josh fingered the problem there --- you need a BEFORE trigger if you
want to affect the data that will be stored.  I had missed that little
mistake :-(

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] query assistance

[Michael Glaesemann]

Hi Jodi,

On Wednesday, November 5, 2003, at 12:16 AM, Jodi Kanter wrote:

Is there a straight forward way to pull out duplicates in a particular field given a value in another field?
For example, I have a table that lists users and study names associated with those users. Each user can have one or more study names.

If I understand you correctly, this is the table you're interested in. Your public.study table doesn't include any users as far as I can tell (though please correct me if I'm misunderstanding you).

       Table "public.study"
    Column    |    Type |    Modifiers
--+-+--
 sty_pk   | integer | not null default nextval('pk_seq'::text)
 study_name   | character varying(128)  | not null
 start_date   | timestamp without time zone |
 sty_comments | text    |
 created_by   | integer |
Indexes: study_pkey primary key btree (sty_pk)

I think something like this is what you're looking for:

SELECT user, study_name, COUNT(*)
FROM 
GROUP BY user, study_name
HAVING COUNT(*) > 1;

where the FROM clause lists the table linking users and study_names.

Does this help?

Michael
grzm myrealbox com

[SQL] UNSUBSCRIBE

[Atul Pedgaonkar]

UNSUBSCRIBE

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] connectby

[Benoît BOURNON]

I use postgresql 7.2.3 
How can I use connectby ?? 

Must I install files ? or packages ? or it is recommanded to upgrade
dataserver ?


George Essig wrote:

  
hi

I have menu table:
  id  | integer | not null default 
nextval('public.menu_id_seq'::text)
  parent_id   | integer |
  description | text|

I do select:
test=> SELECT * FROM connectby('menu','id','parent_id','2',0,'~') t(id 
integer, parent_id integer, level int, branch text);
  id | parent_id | level |   branch
+---+---+-
   2 |   | 0 | 2
   4 | 2 | 1 | 2~4
   7 | 4 | 2 | 2~4~7
  10 | 7 | 3 | 2~4~7~10
  16 |10 | 4 | 2~4~7~10~16
   9 | 4 | 2 | 2~4~9

How am I able to select description file from menu table, not only id, 
parent_id, level, branch fields?

-- 
WBR, sector119

  
  
Try a join with the original table:

SELECT t.*, description FROM connectby('menu','id','parent_id','2',0,'~') 
AS t(id integer, parent_id integer, level int, branch text), menu 
WHERE t.id = menu.id

George Essig

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]