[SQL] select result into string's array
Hi, I need to store the result of select into an array of string: /create or replace function search_engine.test/ /(/ /)/ /returns integer as $$/ /declare/ /m_array text[];/ /begin/ /for m_array in select * from my_table loop/ /raise notice 'valor 1: %',m_array;/ /end loop;/ /return 1;/ /end; $$ LANGUAGE plpgsql;/ This launch this errors: /ERROR: array value must start with { or dimension information SQL state: 22P02 Context: PL/pgSQL function test line 4 at FOR over SELECT rows/ Is it possible do this?? May I choose another way? Thanks in advance Alberto, -- 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] select result into string's array
In response to Alberto Asuero Arroyo : Hi, I need to store the result of select into an array of string: test=*# select * from foo; t -- foo bar batz (3 rows) test=*# select array_agg(t) from foo; array_agg {foo,bar,batz} (1 row) Helps that? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) -- 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] select result into string's array
Hello. You should use an array constructor: DECLARE m_array text[]; [..] BEGIN FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP [..] END LOOP; Regards, Igrishin Dmitriy. 2009/10/9 Alberto Asuero Arroyo albertoasu...@gmail.com Hi, I need to store the result of select into an array of string: /create or replace function search_engine.test/ /(/ /)/ /returns integer as $$/ /declare/ /m_array text[];/ /begin/ /for m_array in select * from my_table loop/ /raise notice 'valor 1: %',m_array;/ /end loop;/ /return 1;/ /end; $$ LANGUAGE plpgsql;/ This launch this errors: /ERROR: array value must start with { or dimension information SQL state: 22P02 Context: PL/pgSQL function test line 4 at FOR over SELECT rows/ Is it possible do this?? May I choose another way? Thanks in advance Alberto, -- 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] select result into string's array
Dmitriy Igrishin wrote: Hello. You should use an array constructor: DECLARE m_array text[]; [..] BEGIN FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP [..] END LOOP; Regards, Igrishin Dmitriy. 2009/10/9 Alberto Asuero Arroyo albertoasu...@gmail.com mailto:albertoasu...@gmail.com Hi, I need to store the result of select into an array of string: /create or replace function search_engine.test/ /(/ /)/ /returns integer as $$/ /declare/ /m_array text[];/ /begin/ /for m_array in select * from my_table loop/ /raise notice 'valor 1: %',m_array;/ /end loop;/ /return 1;/ /end; $$ LANGUAGE plpgsql;/ This launch this errors: /ERROR: array value must start with { or dimension information SQL state: 22P02 Context: PL/pgSQL function test line 4 at FOR over SELECT rows/ Is it possible do this?? May I choose another way? Thanks in advance Alberto, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org mailto:pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql It's has been really useful for my to solve the dinamic Record Introspection problem that I had. Thanks, Alberto -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Existential quantifier
Consider the attached schema (filmstars.sql), which is a poorly designed database of films and actors. The following query gives me a list of films in which either Charlie or Martin Sheen starred: select fs.film.title, fs.film.year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen' group by fs.film.title, fs.film.year; Is there a way to do this without the group by clause? DES -- Dag-Erling Smørgrav - d...@des.no drop schema fs cascade; create schema fs; create table fs.film ( id serial not null primary key, title varchar not null unique, year integer not null ); create table fs.star ( id serial not null primary key, film integer not null references fs.film(id), last varchar not null, first varchar not null, unique (film, last, first) ); insert into fs.film(title, year) values ('Apocalypse Now', 1979); insert into fs.star(film, last, first) select id, 'Sheen', 'Martin' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Brando', 'Marlon' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Duvall', 'Robert' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Ford', 'Harrison' from fs.film where title = 'Apocalypse Now'; insert into fs.film(title, year) values ('Blade Runner', 1982); insert into fs.star(film, last, first) select id, 'Ford', 'Harrison' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Young', 'Sean' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Hauer', 'Rutger' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Hannah', 'Daryl' from fs.film where title = 'Blade Runner'; insert into fs.film(title, year) values ('Platoon', 1986); insert into fs.star(film, last, first) select id, 'Sheen', 'Charlie' from fs.film where title = 'Platoon'; insert into fs.star(film, last, first) select id, 'Dafoe', 'Willem' from fs.film where title = 'Platoon'; insert into fs.star(film, last, first) select id, 'Berenger', 'Tom' from fs.film where title = 'Platoon'; insert into fs.film(title, year) values ('Wall Street', 1987); insert into fs.star(film, last, first) select id, 'Douglas', 'Michael' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Sheen', 'Charlie' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Hannah', 'Daryl' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Sheen', 'Martin' from fs.film where title = 'Wall Street'; -- 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] Existential quantifier
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Sm??rgrav wrote: Consider the attached schema (filmstars.sql), which is a poorly designed database of films and actors. The following query gives me a list of films in which either Charlie or Martin Sheen starred: select fs.film.title, fs.film.year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen' group by fs.film.title, fs.film.year; Is there a way to do this without the group by clause? Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); -- 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] Existential quantifier
you could use distinct on select distinct on (fs.film.title, fs.film.year ) title, year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen'; On Sat, 2009-10-10 at 00:53 +0200, Dag-Erling Smørgrav wrote: Consider the attached schema (filmstars.sql), which is a poorly designed database of films and actors. The following query gives me a list of films in which either Charlie or Martin Sheen starred: select fs.film.title, fs.film.year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen' group by fs.film.title, fs.film.year; Is there a way to do this without the group by clause? DES plain text document attachment (filmstars.sql) drop schema fs cascade; create schema fs; create table fs.film ( id serial not null primary key, title varchar not null unique, year integer not null ); create table fs.star ( id serial not null primary key, film integer not null references fs.film(id), last varchar not null, first varchar not null, unique (film, last, first) ); insert into fs.film(title, year) values ('Apocalypse Now', 1979); insert into fs.star(film, last, first) select id, 'Sheen', 'Martin' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Brando', 'Marlon' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Duvall', 'Robert' from fs.film where title = 'Apocalypse Now'; insert into fs.star(film, last, first) select id, 'Ford', 'Harrison' from fs.film where title = 'Apocalypse Now'; insert into fs.film(title, year) values ('Blade Runner', 1982); insert into fs.star(film, last, first) select id, 'Ford', 'Harrison' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Young', 'Sean' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Hauer', 'Rutger' from fs.film where title = 'Blade Runner'; insert into fs.star(film, last, first) select id, 'Hannah', 'Daryl' from fs.film where title = 'Blade Runner'; insert into fs.film(title, year) values ('Platoon', 1986); insert into fs.star(film, last, first) select id, 'Sheen', 'Charlie' from fs.film where title = 'Platoon'; insert into fs.star(film, last, first) select id, 'Dafoe', 'Willem' from fs.film where title = 'Platoon'; insert into fs.star(film, last, first) select id, 'Berenger', 'Tom' from fs.film where title = 'Platoon'; insert into fs.film(title, year) values ('Wall Street', 1987); insert into fs.star(film, last, first) select id, 'Douglas', 'Michael' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Sheen', 'Charlie' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Hannah', 'Daryl' from fs.film where title = 'Wall Street'; insert into fs.star(film, last, first) select id, 'Sheen', 'Martin' from fs.film where title = 'Wall Street'; -- Rick Albright Senior Quantitative Analyst Insiderscore LLC ralbri...@insiderscore.com -- 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] Existential quantifier
Richard Albright ralbri...@insiderscore.com writes: you could use distinct on select distinct on (fs.film.title, fs.film.year ) title, year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen'; Thanks, I didn't know about distinct on. This version uses the primary (surrogage) key: select distinct on (fs.film.id) fs.film.title, fs.film.year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen'; DES -- Dag-Erling Smørgrav - d...@des.no -- 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] Existential quantifier
Stephan Szabo ssz...@megazone.bigpanda.com writes: Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); Ah, that was exactly what I was looking for. select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); ITYM select fs.film.title, fs.film.year from fs.film where fs.film.id in ( select fs.star.film from fs.star where fs.star.last = 'Sheen' ); (missing FROM) DES -- Dag-Erling Smørgrav - d...@des.no -- 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] Existential quantifier
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Sm??rgrav wrote: Stephan Szabo ssz...@megazone.bigpanda.com writes: Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); Ah, that was exactly what I was looking for. select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); ITYM select fs.film.title, fs.film.year from fs.film where fs.film.id in ( select fs.star.film from fs.star where fs.star.last = 'Sheen' ); (missing FROM) Yeah, that'd be necessary. You might want to try them on a realistic data set to see how the various options are planned. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql