postgresql

create table rank(
a varchar(100),
i int4
);

alter table rank
add constraint pk_rank primary key(a)
;

create index ix_rank on rank(i);

insert into rank(a,i)
select 'John',1
union
select 'paul',2
union
select 'george',3
union
select 'ringo',4
union
select 'brian',5;



create table listword(
i int4,
worded varchar(20)
);

alter table listword
add constraint pk_listword primary key(i)
;

create index ix_listword on listword(UPPER(worded)); -- functional index there is no equivalent in MS SQL


select * from listword where UPPER(worded) = 'ONE';



insert into listword(i, worded)
select 1, 'one'
union
select 2, 'two'
union
select 3, 'three'
union
select 4, 'four'
union
select 5, 'five';





create or replace function ListRank_FuncOne(z int, out xx varchar(200), out yy int4)
as $$

SELECT * from rank WHERE i = $1
$$
language 'sql';





create or replace function ListRank_FuncMore(z int) returns setof rank
as $$
select * from rank where i > $1; -- z
$$ language 'sql';



create or replace function ListRank_SpMore(z int) returns setof rank
as $$
declare r record;
begin
for r in select * from rank where i > z
loop
return next r;
end loop;
end;
$$ language 'plpgsql';


-- scalar-valued function in mssql server parlance
CREATE OR REPLACE FUNCTION WordEquiv(m_z INT)
RETURNS VARCHAR
AS
$$
DECLARE m_S VARCHAR;
BEGIN
/* method 1:
SELECT worded INTO m_S
FROM ListWord
WHERE i = m_Z;
RETURN m_S;*/

-- method 2
RETURN (SELECT worded FROM ListWord WHERE i = m_Z);
END;
$$ LANGUAGE 'PLPGSQL';



SELECT WordEquiv(1);


-- inline table-valued function in mssql server parlance, but returns only one row
create or replace function ListRankWithWord_FuncOne(z int, out xx varchar(200), out yy int4, out wordrank varchar(200), out beatles varchar(200))
as $$

SELECT
rank.a, rank.i,
listword.worded as blahblahblah,
rank.a || listword.worded || rank.a
from rank
inner join listword using(i)
WHERE i = $1
$$
language 'sql';






CREATE TYPE RankWithWord
AS
(
A VARCHAR(200),
I INT4,
Word VARCHAR(400),
HeyHey vARCHAR(200)
);





-- inline table-valued function in mssql server parlance, albeit more difficult in posgtresql(we have to set up a type)
-- seems the recommended approach is to name the type almost similar to the name of the function
create or replace function ListRankWithWord_FuncMore(z int) returns setof rankwithword
as $$
SELECT
rank.a, rank.i,
listword.worded as blahblahblah,
rank.a || listword.worded || rank.a
from rank
inner join listword using(i)
where i > $1; -- z

$$ language 'sql';



-- multi-statement table-valued function in mssql server parlance, albeit more difficult in postgresql(we have to set up a type)
create or replace function ListRankWithWord_SpMore(z int) returns setof rankwithword
as $$
declare r rankwithword;
begin
for r

IN

SELECT
rank.a, rank.i,
listword.worded as wordrank ,
rank.a || rank.a || listword.worded as whatThe
from rank
inner join listword using(i)
where i > z

loop
return next r;
end loop;

r.a = 'michaelbuen';
r.i = 0;
r.word = 'solved';
r.heyhey = 'yeah';
return next r;
end;
$$ language 'plpgsql';





select * from ListRank_FuncOne(2);

select * from ListRank_FuncMore(2);

select * from ListRank_SpMore(2);

select * from ListRankWithWord_FuncOne(2);

select * from ListRankWithWord_FuncMore(2);

select * from ListRankWithWord_SpMore(2);




create local temp table tmp_word as select * from listword;

select * from tmp_word