Helper postgres function `safe_auth_json`

I was trying to make a helper function to convert a

auth.users

auth.users

row to json stripping away sensitive data.

Using just

row_to_json

row_to_json

row_to_json

row_to_json within the trigger function works perfectly fine.

It would just make things "cleaner" and I'd especially like to understand why I don't get it working

Following a few versions I tried, probably not all versions, though all error trying them in the Dashboards SQL Editor

CREATE FUNCTION safe_json(_row json)
  RETURNS json
AS $$
  BEGIN
  RETURN row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
  END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION safe_json(_row json)
  RETURNS json
AS $$
  BEGIN
  RETURN row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
  END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION safe_json(_row json)
  RETURNS json
AS $$
  BEGIN
  RETURN row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
  END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION safe_json(_row json)
  RETURNS json
AS $$
  BEGIN
  RETURN row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(*)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'] FROM _row;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(*)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'] FROM _row;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(*)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'] FROM _row;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(*)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'] FROM _row;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT row_to_json(_row)::jsonb-ARRAY['encrypted_password', 'confirmation_token', 'recovery_token', 'phone_change_token', 'email_change_token_new', 'reauthentication_token', 'email_change_token_current'];
$$ LANGUAGE sql;

jdgamble555•10/1/22, 1:11 PM

try:

SELECT to_json('encrypted_password', ....) as users

SELECT to_json('encrypted_password', ....) as users

If you want all your data in JSON form and not array form

CanRauOP•10/1/22, 1:31 PM

hmm, doesn't or not sure how to use it

jdgamble555•10/1/22, 1:53 PM

What do you mean? What errors are you getting, what did you try?

CanRauOP•10/1/22, 3:07 PM

yea sorry, was a little distracted

First try, which seems incomplete, but you never know

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT to_json('encrypted_password') as users
$$ LANGUAGE sql;

SELECT safe_auth_json(u) AS safe FROM auth.users u;

// => Failed to run sql query: could not determine polymorphic type because input has type unknown

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT to_json('encrypted_password') as users
$$ LANGUAGE sql;

SELECT safe_auth_json(u) AS safe FROM auth.users u;

// => Failed to run sql query: could not determine polymorphic type because input has type unknown

and

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT to_json(_row.encrypted_password) as users
$$ LANGUAGE sql;

SELECT safe_auth_json(u) AS safe FROM auth.users u;

// => Success. No rows returned.

CREATE OR REPLACE FUNCTION safe_auth_json(_row auth.users)
  RETURNS json
AS $$
  SELECT to_json(_row.encrypted_password) as users
$$ LANGUAGE sql;

SELECT safe_auth_json(u) AS safe FROM auth.users u;

// => Success. No rows returned.

Helper postgres function `safe_auth_json`

Similar Threads

Similar Threads

Similar Threads