[SQL] Selecting data from XML

[Victor Sterpu]

If I have a XML like this






can I write a query that will output the columns names and values like this?

code;validFrom;validTo
--
CLIN102;1980-02-23;
CLIN103;1980-02-23;2012-01-01

Thank you

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Need help with a special JOIN

[Victor Sterpu]

This is a way to do it, but things will change if you have many
attributes/object

SELECT o.*, COALESCE(a1.value, a2.value)
FROM objects AS o
LEFT JOIN attributes AS a1 ON (a1.object_id = o.id)
LEFT JOIN attributes AS a2 ON (a2.object_id = 0);

On 29.09.2012 19:02, Andreas wrote:

Hi,

asume I've got 2 tables

objects ( id int, name text )
attributes ( object_id int, value int )

attributes   has a default entry with object_id = 0 and some other 
where another value should be used.


e.g.
objects
(   1,   'A'   ),
(   2,   'B'   ),
(   3,   'C'   )

attributes
(   0,   42   ),
(   2,   99   )

The result of the join should look like this:

object_id, name, value
1,   'A',   42
2,   'B',   99
3,   'C',   42


I could figure something out with 2 JOINs, UNION and some DISTINCT ON 
but this would make my real query rather chunky.   :(


Is there an elegant way to get this?






--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Trigger triggered from a foreign key

[Victor Sterpu]

I have this trigger that works fine. The trigger prevents the deletion 
of the last record.
But I want skip this trigger execution when the delete is done from a 
external key.

How can I do this?

This is the fk
ALTER TABLE focgdepartment
  ADD CONSTRAINT fk_focgdep_idfocg FOREIGN KEY (idfocg)
  REFERENCES focg (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE CASCADE;

This is the trigger
CREATE FUNCTION check_focgdepartment_delete_restricted() RETURNS trigger 
AS $check_focgdepartment_delete_restricted$

BEGIN
IF ( (SELECT count(*) FROM focgdepartment WHERE idfocg = 
OLD.idfocg)=1)

THEN RAISE EXCEPTION 'Last record can not be deleted';
END IF;
RETURN OLD;
END;
$check_focgdepartment_delete_restricted$
LANGUAGE plpgsql;
CREATE TRIGGER focgdepartment_delete_restricted BEFORE DELETE ON 
focgdepartment FOR EACH ROW EXECUTE PROCEDURE 
check_focgdepartment_delete_restricted();



Thank you


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] ZIP function

[Victor Sterpu]

Is there a function that will give the resulting zip content for a 
string?

Like SELECT zip('test data');?

Thank you.

[SQL] function array_to_string(text[]) does not exist

[Victor Sterpu]

Hello

When I run :
SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min AS CHAR), 
'-', CAST(ltrv1.val_max AS CHAR), ' ', ltrv1.comentarii)))

FROM lab_tests_reference_values ltrv1
GROUP BY ltrv1.val_min, ltrv1.val_max, ltrv1.comentarii;

I get the error:
ERROR:  function array_to_string(text[]) does not exist
LINE 1: SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min ...
   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.


I tryed to cast but it's still not working.

Thanku you.