Category Archives: Notes

All Articles

What is PostgreSQL?

PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. PostgreSQL is open source database system which supports all the below features.

 

  • complex queries
  • foreign keys
  • triggers
  • views
  • transactional integrity
  • multiversion concurrency control

 

Also, PostgreSQL can be extended by the user in many ways, for example by adding new

  • data types
  • functions
  • operators
  • aggregate functions
  • index methods
  • procedural languages

 

 

License

PostgreSQL can be used, modified, and distributed by anyone free of charge for any purpose, be it private, commercial, or academic

Setting the PowerShell Execution Policy

Problem
Recently I moved PowerShell script files to a production environment and when executing it from the command prompt, I got this error: “File cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details”. In this tip we cover what needs to be done to resolve this issue.

Solution
http://www.mssqltips.com/sqlservertip/2702/setting-the-powershell-execution-policy/

How to make SQL Server View Read Only?

In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (even you can INSERT/UPDATE/DELETE data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting. You can also use views as a security mechanism to restrict the data available to end users

See the below example how we can make the view read only.


--creating a sample table
Create table tbl1
(
	myID int,
	name varchar(10)
)

--inserting data
insert into tbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--creating sample view
create view vwtbl1
as
select * from tbl1

--inserting data using view
insert into vwtbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--altering view to make it readOnly
alter view vwtbl1
as
select myid,name from tbl1
union all
select 0,0 where 1 =0

INSERT/UPDATE/DELETE will fail with the below errors.

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'vwtbl1' failed because it contains a derived or constant field.

Msg 4426, Level 16, State 1, Line 1
View 'vwtbl1' is not updatable because the definition contains a UNION operator.