Postgresql Text Fields get '' when they are nulls

Question

I have a table called art_movimientos

CREATE TABLE public.art_movimientos
(
  cmovimiento bigint NOT NULL DEFAULT nextval('art_movimientos_cmovimiento_seq'::regclass),
  tipo character varying(3) NOT NULL, -- Tipos de Valores:...
  fecha_mov timestamp without time zone NOT NULL,
  documento integer NOT NULL,
  control integer,
  fecha_doc timestamp without time zone NOT NULL,
  corden integer NOT NULL DEFAULT 0,
  calmacen integer NOT NULL,
  calmacen2 integer,
  status character varying(13) NOT NULL DEFAULT 'PENDIENTE'::bpchar, -- PENDIENTE...
  donado integer NOT NULL DEFAULT 0,
  monto_mov numeric(11,2) NOT NULL DEFAULT 0.00,
  monto_desc numeric(11,2) NOT NULL DEFAULT 0.00,
  monto_total numeric(11,2) NOT NULL DEFAULT 0.00,
  observacion text,
  casiento integer,
  crea_user character varying(25),
  crea_date timestamp without time zone,
  mod_user character varying(25),
  mod_date timestamp without time zone,
  cproveedor integer NOT NULL DEFAULT 0,
  CONSTRAINT pk_cmovimiento_art_movimientos PRIMARY KEY (cmovimiento)
  USING INDEX TABLESPACE sistema_index,
  CONSTRAINT fk_calmacen_art_movimientos FOREIGN KEY (calmacen)
      REFERENCES public.almacen (calmacen) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT ck_donado_art_movimientos CHECK (donado = ANY (ARRAY[0, 1])),
  CONSTRAINT ck_monto_total_art_movimientos CHECK (monto_total > 0::numeric AND monto_total >= monto_mov),
  CONSTRAINT ck_status_art_movimientos CHECK (status::text = ANY (ARRAY['PENDIENTE'::character varying, 'PROCESADO'::character varying, 'APROBADO'::character varying, 'CONTABILIZADO'::character varying]::text[])),
  CONSTRAINT ck_tipo_art_movimientos CHECK (tipo::text = ANY (ARRAY['AJE'::character varying, 'AJS'::character varying, 'SI'::character varying, 'CSM'::character varying, 'COI'::character varying, 'COM'::character varying, 'DEV'::character varying, 'TRF'::character varying]::text[]))
)
WITH (
  OIDS=FALSE
);

The Controller get the data and it convert in array to pass to the class:

...    
$_obs =$_POST['observacion']<>'' ? strtoupper($_POST['observacion']) : $_POST['observacion'];
....
array_push($values, $_obs);
...
$mov_dat = $mov->new_mov($values);

The DB Class have the function, it call another function and return the result:

    public function new_mov($array){
            return $mov = $this->db->procedure("insert_art_mov",$array);
        }
...
public function procedure($procedure=false,$rows=false){
        $response=array();
        if($procedure==false){
            $response="Query is Empty!";
        }else{
            $sql = "SELECT ".$procedure."(";
            for ($i=1; $i<=count($rows); $i++){
                $sql .= "$".$i.",";
            }
            $sql = substr($sql,0,-1);
            $sql .= ")";
            //echo $sql;
            $res = pg_prepare($this->linkid, "my_query", $sql);
            $res = @pg_execute($this->linkid, "my_query", $rows);
            if($res){
                while($consF=pg_fetch_assoc($res))
                    array_push($response,$consF[$procedure]);
            }else{
                array_push($response,"ERROR");
                array_push($response,$this->validateOperation());
            }
            $sql = sprintf('DEALLOCATE "%s"',pg_escape_string("my_query"));
             if(!pg_query($sql)) {
                die("Can't query '$sql': " . pg_last_error());
             }
            return $response;
        }
    }

Then i execute the Procedure in the DB:

CREATE OR REPLACE FUNCTION public.insert_art_mov(...IN _observacion text,..)
INSERT INTO art_movimientos(tipo,fecha_mov,documento,control,fecha_doc,corden,cproveedor,calmacen,status,donado,monto_mov,monto_desc,monto_total,observacion,crea_user)
            VALUES(_tipo,NOW(),_documento,_control,_fecha_doc,_corden,_cprov,_calmacen,'PENDIENTE',_donado,MONTO_MOV,_monto_desc,MONTO_TOT,_observacion,_crea_user) RETURNING cmovimiento INTO new_cmov;

But in the table in observacion i see '' (simple cuotes) and i wanna know why?

Answer 1

You will get those single quotes because you do not check for empty strings. What I would recommend and I do this as well is that for every string field pass it to a function to return either data or NULL.

DICLAIMER: Half answer but mostly a guide :)

For the backend, you can try the below function when there is a string that needs to be added:

function chech_string ($str) {
    return (0 != strlen($str)) ? $str : NULL;
}

Or you create your sql string as follow

$sql = "INSERT INTO table(col1, col2) VALUES (';

if (0 != strlen($str1))
    $sql .= "'$str1'";
else
    $sql .= "NULL";

if (0 != strlen($str2))
    $sql .= "'$str2'";
else
    $sql .= "NULL";

    $sql .= ')';

At the end your string would look like

$sql = "INSERT INTO table(col1, col2) VALUES ('STR1', 'STR2')";
OR
$sql = "INSERT INTO table(col1, col2) VALUES ('STR1', NULL)";

So you can use this mething to create you procedure as well

For the front end:

<input type="text" value="<?php echo (0 != strlen($col_val)) ? $col_val : '' ?>"/>

that will give you the result you need in the front end