POSTGRESQL Stored Procedure Combat

N-more SQL statements, but their own time, but everywhere is the pit ah, ah, ah!!!!!!!!!!!!!!!

Want to write a get the latest ID value in the table.

On the Code

CREATE TABLE department (
   ID INT PRIMARY KEY NOT NULL,
   d_code VARCHAR (50),
   d_name VARCHAR (50) NOT NULL,
   d_parentID INT NOT NULL DEFAULT 0
);
--insert into department values (1, ‘001’, ‘office’);
--insert into department values (2, ‘002’, ‘office’, 1);
Now write a stored procedure to get the maximum ID in the table:

drop function f_getNewID (text, text);
create or replace function f_getNewID (myTableName text, myFeildName text) returns integer as $$
declare
    mysql text;
        myID integer;
begin
    mysql: = ‘select max ($ 1) from $ 2’;
        execute mysql into myID using myFeildName, myTableName;
    
    if myID is null or myID = 0 then return 1;
    else return myID + 1;
      end if;
end;
$$ language plpgsql;

-Everyone can try it, the above one will give an error
--select f_getNewID (‘department’, ‘ID’);
-Error!
After reading the official documentation, this is how they use it:

EXECUTE ‘SELECT count (*) FROM mytable WHERE inserted_by = $ 1 AND inserted <= $ 2’
   INTO c
   USING checked_user, checked_date;
Are you sure you see clearly? ? ? ? ?

Are you sure you read the manual after reading it? ? ? ? ?

-Did you watch this?
---------------------------------------
EXECUTE ‘SELECT count (*) FROM‘
    || quote_ident (tabname)
    || ‘WHERE inserted_by = $ 1 AND inserted <= $ 2’
   INTO c
   USING checked_user, checked_date;

-Did you watch this?
---------------------------------------
EXECUTE ‘UPDATE tbl SET‘
        || quote_ident (colname)
        || ‘=‘
        || quote_literal (newvalue)
        || ‘WHERE key =‘
        || quote_literal (keyvalue);

-===============================
-OK, I change
-------------------------------------------------- ----
drop function f_getNewID (text, text);
create or replace function f_getNewID (myTableName text, myFeildName text) returns integer as $$
declare
    mysql text;
    myID integer;
begin
    mysql: = ‘select max (‘
        || quote_ident (myFeildName)
        || ‘) from‘
        || quote_ident (myTableName);
    execute mysql into myID;
    --using myTableName, myFeildName;
    
    if myID is null or myID = 0 then return 1;
    else return myID + 1;
      end if;
end;
$$ language plpgsql;

-================================
-Beautiful and successful!
--But Why?
--Note that objects (table names, field names, etc.) cannot use variables directly, use quote_ident ()
-------------------------------------------------- -----
postgres = # select f_getnewid (‘department’, ‘ID’);
--Error: field "ID" does not exist
-Line 1 select max ("ID") from department
                ^
--Query: select max ("ID") from department
--Background: PL / pgSQL function f_getnewid (text, text) on line 10 of EXECUTE

-=================================
-Under what circumstances, how can ID have double quotes, quotes, numbers, numbers? ? ?
-------------------------------------------------- --------
-Thank you, God: Quan Zongliang @ 飞 象 数据
-Change to this:
postgres = # select f_getnewid (‘department’, ‘id’);
 f_getnewid
------------
          2
(1 line record)
---- Finally succeeded! Is there a difference in capitalization? ? ? --but why? --when typing on the command line
CREATE TABLE role (
   ID INT PRIMARY KEY NOT NULL,
   r_name VARCHAR (50) NOT NULL,
   r_paretnID INT NOT NULL DEFAULT 0
);
-As a result, what I see in pgAdmin is lowercase
--Similarly, if it is created with such a statement under QUERY TOOLS or all fonts are named lowercase
--What if I want to capitalize? ? ? ?
-To write like this
CREATE TABLE "RoleUPER" (
   "ID" INT PRIMARY KEY NOT NULL,
   r_name VARCHAR (50) NOT NULL,
   "r_paretnID" INT NOT NULL DEFAULT 0
);
--Look at the elephant again
-OK!
in conclusion:

1. Stored procedure (FUNCITON) variables can be directly stitched with || Not listed above, here is a chestnut:



create or replace function f_getNewID (myTableName text, myFeildName text) returns integer as $$
declare
    mysql text;
        myID integer;
begin
    mysql: = ‘select max (‘ || $ 2 || ‘) from‘ || $ 1;
        execute mysql into myID using myFeildName, myTableName;
    if myID is null or myID = 0 then return 1;
    else return myID + 1;
      end if;
end;
$$ language plpgsql;
2, the object of the stored procedure can not use variables directly, use quote_ident (objVar)

3, $ 1 $ 2 is the order of FUNCTION parameters, such as $ 1 $ 2 exchanged in 1, no change after USING Result: select max (myTableName) from myFeildname

4. Note: All uppercase letters in SQL statements will become lowercase. If you want to capitalize, use double quotes.

POSTGRESQL stored procedure in action