PostgreSQL

-- stat.rfq_products table:
-- If you frequently query this table based on the rfq_id column, which references the stat.request_for_quotes table, you can create an index on this column for faster lookups.
CREATE INDEX idx_rfq_products_rfq_id ON stat.rfq_products (rfq_id);

SELECT relname, relkind FROM pg_class -- pg_class lists all the kind of objects in the database
       WHERE relkind = 'i'; -- this tells to retrieve only the kind of 'i' which stands for _index_

with recursive numbers AS (
        select 1 as n
          union all
        select n + 1 from numbers where n < 5
)

select * from numbers;

insert into cities(name, location) values ('Rio bravo', point(100, 100));

update cities
  set location = point(200, 200)
  where name = 'Rio bravo'
  returning *; -- returns updated rows

delete from cities
where name ilike 'Rio Bravo'; -- case insensitive

select * from cities;

BEGIN;
UPDATE accounts SET balance = balance - 100.00
 WHERE name = 'Alice';
-- etc etc
COMMIT;
-- If, partway through the transaction, we decide we do not want to commit (perhaps we just noticed that
-- Alice's balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our
-- updates so far will be canceled.





-- It's possible to control the statements in a transaction in a more granular fashion through the use of save-
-- points. Savepoints allow you to selectively discard parts of the transaction, while committing the rest. After
-- defining a savepoint with SAVEPOINT, you can if needed roll back to the savepoint with ROLLBACK TO.
-- All the transaction's database changes between defining the savepoint and rolling back to it are discarded,
-- but changes earlier than the savepoint are kept.
-- Remembering the bank database, suppose we debit $100.00 from Alice's account, and credit Bob's account,
-- only to find later that we should have credited Wally's account. We could do it using savepoints like this:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
 WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
 WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
 WHERE name = 'Wally';
COMMIT;

create view myView as
       select * from weather where city = 'reynosa';

select * from myView;

-- default role in this session is 'postgres'
create role role_x; -- creating role with name role_x
select current_role; -- this outputs 'postgres'
set role role_x; -- set role x as the current role
select current_role; -- outputs 'role_x'

-- this below function just returns a text 
create or replace function public.some_function()
 returns text
    language plpgsql
as $$
    begin
        return 'Hello World';
    end;
$$;


-- executing the function
select some_function(); -- this returns 'Hello World'

-- Now lets restrict the execution of 'some_function' function to an specific roke
-- in this case, we will prevent 'role_x' to execute 'some_function' function.

-- let's change the role
set role role_x;

-- now let's execute the function
select some_function(); -- this will output 'Hello World'

-- let's revoke the permission of execution to role x
-- to achieve this, first let's change the role to a role that
-- has enough permissions to do this, like a superuser role, like 'postgres'
-- which is the default role in PostgreSQL
-- @Note: postgres role is a superuser role created by default during the
-- installation of PostgreSQL. The postgres role has full privileges over
-- the entire database system and can perform any operation, including granting
-- and revoking privileges for other roles. It's similar to the root user in a Unix system.
set role postgres;
revoke execute on function some_function() from role_x;

-- lets verify trying to execute the function from role_x
set role role_x;
select some_function(); -- this still not prevents the execution because this role inherits from 'public' role
-- so let's revoke really the function.
set role postgres;
revoke execute on function some_function() from public;
set role role_x;

DO $$
DECLARE
  result text;
BEGIN
    BEGIN
        result := select some_function();
        RAISE NOTICE 'Function returned: %', result;
    EXCEPTION WHEN INSUFFICIENT_PRIVILEGE THEN
        RAISE WARNING 'An error occurred: %', SQLERRM;
    END;
END;
$$ LANGUAGE plpgsql;