See documentation on Oracle side (same principle).
It operates under a client-server arquitecture , where the server side is composed of:
The client side can be composed of any of the following:
It should be noted that as every incoming connection is handled by postmaster and passed to a server process, once this is done , the communication is establishd directly between the client and Server Process , the postmaster drops off the communication circle.
PL/pgSQL is a Procedural Language extension to Postgres database. SQL is non-procedural language ,in it the programmer only describes what work to perform, how to perform the work is left to the "Planner/Optimizer", in contrast PL/pgSQL is like any 3GL procedural language, it requires step by step instructions defininig what to do next.
PL/pgSQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL.In Postgres it is also possible to create functions in procedural languages like Perl and Tcl, which interspersed with SQL are called PLPerl and PLTcl respectively.
select * ....[everything]
select distinct ....[distinct values in selection]
select .......for update of ...[Aquires a lock]
select * from users where user_id=3 select email,user_name from employees where name='Daniel'
The difference between SQL and PSQL is very stright forward, any command which begins with an unquoted backslash (
\ ) is considered a PSQL command, these commands are sometimes refered to as:
Meta-commands ,some commands are:
\connect : This commands connects to particular database specified as the argument.
\d : Shows all columns of the specified table,view,index or sequence, the types and special attributes related to it, the
\d+ command could also be used which shows any comments associated with the table,view or index.
\df (regexp): This command lists all the functions with arguments and return types that match the
regular expression .
\distvS (regexp): In general the
\d command could be followed by a letter
i s t v or
S where they respectively mean: index, sequence, table, view and System table,if a
regular expression is provided it limits the search to that string, you may also add the
+ (plus sign) to indicate that extra comments be displayed.
\dp : Is used to display permissions on the database. a
\e : Escapes the psql environment and opens a text editor in the terminal ,it defaults to the text editor specified by the environment variables:PSQL_EDITOR,EDITOR or VISUAL (in that order).
\echo (text): Echoes the text back to the terminal, normally used when the
\o command is activated.
\encoding (type): Sets the current encoding, if no type is specified it displays the currents client (psql) encoding.
\i (file): Reads input from the specified file,this is typically used in conjunction with the
\l : List all the databases in the current database system
\o file : Pipes out all output to a specific file.
\p : Displays current buffer
\pset (parameters): This command along with various parameters determines how the output will displayed on the terminal
NOTE: These are only the basic commands, there are various other shortcuts that are not mentioned, you should refer to the
psql manual page for the complete list.
select animals from zoo where classification='mammals';
select cars from lot where year > to_date('2000-01-01','YYYY-MM-DD') ;
select boat_names from triton where boat_names like 'ki%'; (The
% acts as a wildcard,SQL compliance with Oracle)
select houses from city where city='Ensenada' order by block_id; select cuartos from hoteles order by precio desc; select cuarto from hoteles order by precio asc;[asc is the default, SQL complianance with Oracle]