Si encuentras alguna parte que no entiendes puedes dejarnos un comentario y trataremos de ayudarte lo más rápido posible.
Solución:
Devolver columnas seleccionadas
CREATE OR REPLACE FUNCTION get_user_by_username(_username text
, _online bool DEFAULT false)
RETURNS TABLE (
user_id int
, user_name varchar
, last_activity timestamptz
)
LANGUAGE plpgsql AS
$func$
BEGIN
IF _online THEN
RETURN QUERY
UPDATE users u
SET last_activity = current_timestamp -- ts with time zone
WHERE u.user_name = _username
RETURNING u.user_id
, u.user_name
, u.last_activity;
ELSE
RETURN QUERY
SELECT u.user_id
, u.user_name
, u.last_activity
FROM users u
WHERE u.user_name = _username;
END IF;
END
$func$;
Llamar:
SELECT * FROM get_user_by_username('myuser', true);
Tu tenias DECLARE result record; pero no usó la variable. Eliminé la cruft.
Puede devolver el registro directamente desde el UPDATEque es mucho más rápido que llamar a un adicional SELECT declaración. Usar RETURN QUERY y UPDATE con un RETURNING cláusula.
Si el usuario no es _onlinepor defecto a un plano SELECT. Este es también el valor predeterminado (seguro) si se omite el segundo parámetro, lo cual solo es posible después de proporcionar ese valor predeterminado con DEFAULT false en la definición de la función.
Si no califica los nombres de las columnas en la tabla (tablename.columnname) en consultas dentro de la función, tenga cuidado con conflictos de nombres entre nombres de columna y parámetros con nombre, que son visibles (la mayoría) en todas partes dentro de una función.
También puede evitar este tipo de conflictos utilizando referencias posicionales ($n) para los parámetros. O usa un prefix que Tú nunca uso para nombres de columna: como un guión bajo (_username).
Si users.username se define único en tu mesa, entonces LIMIT 1 en la segunda consulta es simplemente cruft. Si esto es noentonces la UPDATE puede actualizar varias filas, lo que es más probable incorrecto. asumo un unico username y recortar el ruido.
Definir el tipo de retorno de la función (como demostró @ertx) o debe proporcionar una lista de definición de columna con cada llamada de función, lo cual es incómodo.
Crear un tipo para ese propósito (como propuso @ertx) es un enfoque válido, pero probablemente sea excesivo para una sola función. Ese era el camino a seguir en las versiones antiguas de Postgres antes de que tuviéramos RETURNS TABLE para ese propósito, como se demostró anteriormente.
Ustedes no necesita un bucle para esta sencilla función.
Cada función necesita una declaración de lenguaje. LANGUAGE plpgsql en este caso.
yo suelo timestamptz (timestamp with time zone) en vez de timestamp (timestamp without time zone), que es el valor predeterminado sensato. Ver:
- Ignorando las zonas horarias por completo en Rails y PostgreSQL
Devolver (conjunto de) filas completas
Regresar todas las columnas de la mesa existente users, hay una forma más sencilla. Postgres define automáticamente un tipo compuesto del mismo nombre para cada tabla. Solo usa RETURNS SETOF users para simplificar enormemente la consulta:
CREATE OR REPLACE FUNCTION get_user_by_username(_username text
, _online bool DEFAULT false)
RETURNS SETOF users
LANGUAGE plpgsql AS
$func$
BEGIN
IF _online THEN
RETURN QUERY
UPDATE users u
SET last_activity = current_timestamp
WHERE u.user_name = _username
RETURNING u.*;
ELSE
RETURN QUERY
SELECT *
FROM users u
WHERE u.user_name = _username;
END IF;
END
$func$;
Devuelve toda la fila más la adición personalizada
Para abordar la pregunta agregada por TheRealChx101 en un comentario a continuación:
¿Qué sucede si también tiene un valor calculado además de una tabla completa?
No tan simple, pero factible. Podemos enviar todo el tipo de fila como una campo, y agregue más:
CREATE OR REPLACE FUNCTION get_user_by_username3(_username text
, _online bool DEFAULT false)
RETURNS TABLE (
users_row users
, custom_addition text
)
LANGUAGE plpgsql AS
$func$
BEGIN
IF _online THEN
RETURN QUERY
UPDATE users u
SET last_activity = current_timestamp -- ts with time zone
WHERE u.user_name = _username
RETURNING u -- whole row
, u.user_name || u.user_id;
ELSE
RETURN QUERY
SELECT u, u.user_name || u.user_id
FROM users u
WHERE u.user_name = _username;
END IF;
END
$func$;
La “magia” está en la llamada a la función, donde (opcionalmente) descomponemos el tipo de fila:
SELECT (users_row).*, custom_addition FROM get_user_by_username('foo', true);
db<>violín aquí (mostrando todo)
Si necesitas algo más “dinámico”, considera:
- Refactorice una función PL/pgSQL para devolver el resultado de varias consultas SELECT
si desea crear una función que devuelva un conjunto de registros, deberá definir los tipos de columna en su declaración de selección
Más información
Su consulta debería ser algo como esto:
select * from get_user_by_username('Username', True) as
f(user_id integer, user_name varchar, last_activity, varchar, created date, email archar, approved boolean, last_lockout timestamp, last_login timestamp,
last_password_changed timestamp, password_question varchar, comment varchar)
(probablemente necesitará cambiar los tipos de datos)
Yo personalmente prefiero el enfoque de tipos. asegura que si se edita la función, todas las consultas arrojarán resultados correctos. Puede ser una molestia porque cada vez que modifique los argumentos de la función, también deberá volver a crear/eliminar tipos.
P.ej:
CREATE TYPE return_type as
(user_id integer,
user_name varchar,
last_activity varchar,
created timestamp,
email varchar,
approved boolean,
last_lockout timestamp ,
last_login timestamp,
last_password_changed timestamp,
password_question varchar,
comment varchar);
create or replace function get_user_by_username( username varchar(250), online
boolean) returns setof return_type as $$
declare _rec return_type;
begin
if online then
update users
set last_activity = current_timestamp
where user_name = username;
end if;
for _rec in select
user_id,
user_name,
last_activity,
created,
email,
approved,
last_lockout,
last_login,
last_password_changed,
password_question,
comment
from
users
where
user_name = username
limit 1
loop
return next _rec;
end loop
end;
$$ language plpgsql;
Acuérdate de que tienes el privilegio decir si te fue de ayuda.