How to know size of a binarie in sql

datalength(binary)

Kill a process with a command on Windows

taskkill /F /IM notepad.exe >nul 2>&1

Select the end of a chain

#:~$ toto="bonjour le monde"
#:~$ echo ${toto:$((${#toto}-10))}
r le monde

${var:x} : extract sub chain of $var from char n° x to the end.
$((….)) : allow to do calculation

Grant rights on many tables

-- pgx_grant(PRIVILEGE, TABLES, USER)
--  Grants PRIVILEGE to USER on objects like TABLES%
--  Grants to tables, views and sequences.
--
CREATE FUNCTION pgx_grant(text,text,text) returns int4 as '
DECLARE
  priv ALIAS FOR $1;
  patt ALIAS FOR $2;
  user ALIAS FOR $3;
  obj  record;
  num  integer;
BEGIN
  num:=0;
  FOR obj IN SELECT relname FROM pg_class
  WHERE relname LIKE patt || \'%\' AND relkind in (\'r\',\'v\',\'S\') LOOP
    EXECUTE \'GRANT \' || priv || \' ON \' || obj.relname || \' TO \' || user;
    num := num + 1;
  END LOOP;
  RETURN num;
END;
' language 'plpgsql';

Create a multi tar with specified size

Create a tar file:

tar cvf - gaim/ | split -b 1024k - gaim.tar
ll gaim.tar*
-rw-r--r--    1 root     root      1048576 Jan 29 16:57 gaim.taraa
-rw-r--r--    1 root     root      1048576 Jan 29 16:57 gaim.tarab
-rw-r--r--    1 root     root      1048576 Jan 29 16:57 gaim.tarac
-rw-r--r--    1 root     root      1048576 Jan 29 16:57 gaim.tarad
-rw-r--r--    1 root     root      1048576 Jan 29 16:57 gaim.tarae
-rw-r--r--    1 root     root       225280 Jan 29 16:57 gaim.taraf

To read it :

cat gaim.tar* | tar tvf -

SIZE may have a multiplier suffix: b 512, kB 1000, K 1024, MB 1000*1000, M 1024*1024, GB 1000*1000*1000, G 1024*1024*1024, and so on for T, P, E, Z,Y.

My first plpgsql function

At first you need to declare language :

$ createlang plpgsql database

Then connect on postgres :

database=# CREATE FUNCTION a_function () RETURNS int4 AS '
database'# DECLARE
database'# an_integer int4;
database'# BEGIN
database'#    an_integer := 10 * 10;
database'#    return an_integer;
database'# END;
database'# ' LANGUAGE 'plpgsql';

Your first function is now created. You can use it like this :

SELECT a_function() AS output;

Here an exemple of a function :

CREATE OR REPLACE FUNCTION b_function() RETURNS SETOF TEXT AS '
DECLARE
    element_id int4;
    elmt_id0 int4;
    elmt_id1 int4;
    element record;
    output text;
    cur_element CURSOR for SELECT * FROM element;
BEGIN
    OPEN cur_element;
    LOOP
        FETCH cur_element INTO element_id, elmt_id0, elmt_id1;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        DELETE FROM alpha WHERE element_id=element_id;
        DELETE FROM country WHERE element_id=element_id;
        FOR element in SELECT * FROM element WHERE elmt_id=elmt_id0 OR elmt_id=elmt_id1 LOOP
            output := element.element_home_dir || element.element_uri;
            return next output;
        END LOOP;
    END LOOP;
    CLOSE cur_element;
    RETURN;
END;
' LANGUAGE 'plpgsql';
SELECT * from b_function() GROUP BY b_function;

Hashes of hashes

#!/usr/bin/perl
use strict;
my %toto;
$toto{3}{4} = 'jj';
my $un;
my $deux;

foreach $un (keys %toto) {
foreach $deux (keys %{$toto{$un}} ) {
print $toto{$un}{$deux}."\n";
}
}

Delete an Oracle Database

$ sqlplus "/ as sysdba"

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

SQL> exit

List a column and use it to make an operation with TSQL

DECLARE @tags varchar(200)
DECLARE @K int
SET @K=1

DECLARE MyCursor CURSOR
FOR
   SELECT columns
   FROM table

OPEN MyCursor

-- read the first record
FETCH MyCursor INTO @tags

-- loop
WHILE @@fetch_Status = 0
BEGIN
   INSERT INTO table2 (id, new_tags) VALUES
		(@K, @tags);
   SET @K = @K + 1;
-- read next record
   FETCH MyCursor INTO @tags
END

CLOSE myCursor

DEALLOCATE myCursor

Source : http://sqlpro.developpez.com/cours/sqlserver/transactsql/

Ssh connection without password

on client side:

ssh-keygen -t rsa

on server side:
copy the public key from client to home directory that you want to be identified
then

cat id_rsa.pub >> ~/.ssh/authorized_keys

options :
If strict_mode, chmod 600 ~/.ssh/authorized_keys
If Authentication refused : PreferredAuthentications publickey