Sempre grazie, siete davvero disponibilissimi.
1) Weierstrass, ti volevo fare una domanda, ma proprio a te che sei un grande matematico. Concordi con me che l’espressione:
and ((input_id is null) or (eventi.id_utente = input_id))
sia quanto di più illogico si possa scrivere?
In matematica l'intersezione di due insiemi di cui uno è vuoto, è l'insieme vuoto mentre in informatica, con la funzione 'select' l'intersezione di due insiemi di cui uno è vuoto, è l'insieme pieno.
2) returns table obbliga alla funzione select la restituzione di una tabella. Si oppone al comando returns setof che restituisce una tabella in cui i valori di uno stesso record appartenenti a colonne diverse risiedono in un’unica cella separati da una virgola. Con setof le tabelle con N colonne vengono trasformate in tabelle con 1 solo colonna. Dato che il mio obiettivo è passare il risultato della query a java devo evitare setof, giusto?
3) plpgsql è molto più potente di sql oppure è semplicemente diverso?
4) Altra cosa che non capisco è l’assegnazione delle variabili che definiscono un numero intero. Se nello schema del database scrivo ‘id bigserial not null’ nella funzione che sto scrivendo cosa devo usare? E’ giusto scrivere ‘id bigint’ oppure devo scrivere ‘id integer’? Se nello schema ho ‘titolo varchar(150) not null,’ nella funzione cosa devo usare? E’ giusto/utile/conveniente riportare quel 150 anche nella funzione?
5) Ho provato a correggere il vostro codice per ottenere la tabella ma sql mi premia con un errore?
[42601] ERRORE: la query non ha una destinazione per i dati restituiti Suggerimento: Se vuoi scartare i risultati di una SELECT, utilizza PERFORM. Dove: funzione PL/pgSQL g(integer,character varying,character varying,text,character varying,timestamp without time zone,timestamp without time zone,character varying) riga 5 a istruzione SQL
create or replace function g (
input_id integer,
input_nome character varying,
input_titolo character varying,
input_descrizione text,
input_luogo character varying,
input_data_inf timestamp without time zone,
input_data_sup timestamp without time zone,
input_stato character varying
)
returns table (
id integer,
id_utente integer,
titolo character varying,
descrizione text,
luogo character varying,
data timestamp without time zone,
stato character varying
)
language plpgsql
as $$
declare
-- variabili usate nel codice
begin
select notizie.* from notizie, utenti
where 1=1
and ((input_id is null) or (notizie.id_utente = input_id))
and ((input_nome is null) or (utenti.nome = input_nome and notizie.id_utente = utenti.id))
and ((input_titolo is null) or (lower(notizie.titolo) like lower(concat('%',input_titolo,'%'))))
and ((input_descrizione is null) or (lower(notizie.descrizione) like lower(concat('%',input_descrizione,'%'))))
and ((input_luogo is null) or (lower(notizie.luogo) like lower(concat('%',input_luogo,'%'))))
and ((input_data_inf is null) or (notizie.data >= input_data_inf))
and ((input_data_sup is null) or (notizie.data < input_data_sup ))
and ((input_stato is null) or (notizie.stato = input_stato))
order by notizie.data asc;
end; $$
select g (
null,
null,
'p',
null,
null,
null,
null,
null
);
Qualcuno sa dirmi dove sbaglio?
Se vi state chiedendo perché uso plpgsql la ragione è semplice, vorrei aggiungere un paio di cicli if.
Questo codice qui sotto funziona ma ha 2 problemi:
1) non ritorna una tabella;
2) nella risposta ci sono record ripetuti e questo mi risulta assolutamente incomprensibile.
CREATE OR REPLACE FUNCTION g2(
input_id INTEGER,
input_nome VARCHAR(100),
input_titolo VARCHAR(150),
input_descrizione TEXT,
input_luogo VARCHAR(100),
input_data_inf TIMESTAMP,
input_data_sup TIMESTAMP,
input_stato VARCHAR(100)
)
RETURNS SETOF notizie
AS
$$
SELECT notizie.* FROM notizie, utenti
WHERE 1=1
AND ((input_id IS NULL) OR (notizie.id_utente = input_id))
AND ((input_nome IS NULL) OR (utenti.nome = input_nome AND notizie.id_utente = utenti.id))
AND ((input_titolo IS NULL) OR (LOWER(notizie.titolo) LIKE LOWER(CONCAT('%',input_titolo,'%'))))
AND ((input_descrizione IS NULL) OR (LOWER(notizie.descrizione) LIKE LOWER(CONCAT('%',input_descrizione,'%'))))
AND ((input_luogo IS NULL) OR (LOWER(notizie.luogo) LIKE LOWER(CONCAT('%',input_luogo,'%'))))
AND ((input_data_inf IS NULL) OR (notizie.data >= input_data_inf))
AND ((input_data_sup IS NULL) OR (notizie.data < input_data_sup ))
AND ((input_stato IS NULL) OR (notizie.stato = input_stato))
ORDER BY notizie.data ASC;
$$ LANGUAGE sql;
SELECT g2(
null,
null,
'p',
null,
null,
null,
null,
null
);
Altro test:
create or replace function g3 (
input_id integer,
input_nome character varying,
input_titolo character varying,
input_descrizione text,
input_luogo character varying,
input_data_inf timestamp without time zone,
input_data_sup timestamp without time zone,
input_stato character varying
)
returns table (
id integer,
id_utente integer,
titolo character varying,
descrizione text,
luogo character varying,
data timestamp without time zone,
stato character varying
)
language plpgsql
as $$
declare
-- variabili usate nel codice
begin
if (input_nome is null) then
select notizie.* from notizie
where 1=1
and ((input_id is null) or (notizie.id_utente = input_id))
and ((input_titolo is null) or (lower(notizie.titolo) like lower(concat('%',input_titolo,'%'))))
and ((input_descrizione is null) or (lower(notizie.descrizione) like lower(concat('%',input_descrizione,'%'))))
and ((input_luogo is null) or (lower(notizie.luogo) like lower(concat('%',input_luogo,'%'))))
and ((input_data_inf is null) or (notizie.data >= input_data_inf))
and ((input_data_sup is null) or (notizie.data < input_data_sup ))
and ((input_stato is null) or (notizie.stato = input_stato))
order by notizie.data asc;
else
select notizie.* from notizie, utenti
where 1=1
and ((input_id is null) or (notizie.id_utente = input_id))
and ((input_nome is null) or (utenti.nome = input_nome and notizie.id_utente = utenti.id))
and ((input_titolo is null) or (lower(notizie.titolo) like lower(concat('%',input_titolo,'%'))))
and ((input_descrizione is null) or (lower(notizie.descrizione) like lower(concat('%',input_descrizione,'%'))))
and ((input_luogo is null) or (lower(notizie.luogo) like lower(concat('%',input_luogo,'%'))))
and ((input_data_inf is null) or (notizie.data >= input_data_inf))
and ((input_data_sup is null) or (notizie.data < input_data_sup ))
and ((input_stato is null) or (notizie.stato = input_stato))
order by notizie.data asc;
end if;
end; $$
select g3 (
null,
null,
'p',
null,
null,
null,
null,
null
);
[42601] ERRORE: la query non ha una destinazione per i dati restituiti Suggerimento: Se vuoi scartare i risultati di una SELECT, utilizza PERFORM. Dove: funzione PL/pgSQL g3(integer,character varying,character varying,text,character varying,timestamp without time zone,timestamp without time zone,character varying) riga 6 a istruzione SQL